Help with Vlook up

[thepulser89]

Hi I am new with the forum. Can you please help me with the below?

A B C
1 ACI02F6R4 WESTPAC
2 ACI02F699 ANZ
3 ACI02F6T4 NAB


I want if the value in column C1 = "Westpac" , then A1 = B1. However, if C1 = "ANZ" or C1 = "NAB", I want to look up value in the table B and C and return the according value from B column to A1. Let say if C1 = ANZ, I want A1 = B2.

I have tried this formula but it doesnt work: =IF(C1="Westpac",B1, Vlookup(C1,B:C,2,0))

Thanks a lot for you help!

[Potatoes and Onions]

I think the formula is not working because the vlookup is looking in column B for the value in c1 which it will not find. Try having a column D that is the same as column B (have D1 as =b1) and then tweak your existing formula for the vlookup range for C:D. That should fix what you're looking for...i think...

[FDibbins]

Hi and welcome to the forum.

1st of all, vlookup cannot look "behind" itself.. Im not sure i completely what you are trying to do, but try this....
=IF(C1="Westpac",B1,INDEX($B$1:$C$3,MATCH(C1,$C$1:$C$3,0),1))

[thepulser89]

Hi thanks heaps guys. They both work. This forum is awesome

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)