VLOOKUP - Issue Finding and Returning Values

0
votes

I'm having an issue with using a VLOOKUP. I have a workbook with accounts and it's information. I'm trying to match it with another list. The issue is the list utilizes the account name sometimes and then the account hierarchy others. So when I'm trying to return columns B & C, it doesn't as I believe VLOOKUP is restricted to searching the 1st column.

enter image description here

So basically the list I'm referencing uses either column A or D from the image above. I'm trying to get it so that it can reference A or D and return columns B and C.

Currently I have this formula, which works fine the account name is used, but not when the account hierarchy group is.

{=VLOOKUP(C8,Sheet5!$A$2:$D$30411,{2,3},FALSE)}

Any help would be greatly appreciated and let me know if more clarification is needed!

1
excelexcel-formula

1 Answers

1
votes

You want to use an INDEX/MATCH this way:

To get the Value from Column B:

=INDEX(Sheet5!B:B,IFERROR(MATCH(C8,Sheet5!A:A,0),MATCH(C8,Sheet5!D:D,0))

So to get C we change the B:B to C:C:

=INDEX(Sheet5!C:C,IFERROR(MATCH(C8,Sheet5!A:A,0),MATCH(C8,Sheet5!D:D,0))