vlookup on excel columns with blanks

[starzdust77]

Hi, i am trying to vlookup on multiple columns to get the Parent customer number

For example, I have the customer number, and i need to lookup the Reference data table to get the Parent customer number.
If Parent customer number is blank, it needs to lookup Obligor.
If both Parent Customer and Obligor is blank, it will just simply need to return the same customer number.
I have a worked example of the 3 scenarios and attached excel.

 

[AlanY]

try

Book1
	A	B	C	D	E	F
1	111	55555	66666		111	66666
2	222	88888			222	88888
3	333				333	333
Sheet1

Cell Formulas
Range	Formula
F1:F3	F1	=IF(AND(VLOOKUP(E1,$A$1:$C$3,3,0)=0,VLOOKUP(E1,$A$1:$C$3,2,0)=0),E1,IF(VLOOKUP(E1,$A$1:$C$3,3,0)=0,VLOOKUP(E1,$A$1:$C$3,2,0),VLOOKUP(E1,$A$1:$C$3,3,0)))

 

[Fluff]

Hi & welcome to MrExcel.

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[starzdust77]

try

Book1
	A	B	C	D	E	F
1	111	55555	66666		111	66666
2	222	88888			222	88888
3	333				333	333
Sheet1

Cell Formulas
Range	Formula
F1:F3	F1	=IF(AND(VLOOKUP(E1,$A$1:$C$3,3,0)=0,VLOOKUP(E1,$A$1:$C$3,2,0)=0),E1,IF(VLOOKUP(E1,$A$1:$C$3,3,0)=0,VLOOKUP(E1,$A$1:$C$3,2,0),VLOOKUP(E1,$A$1:$C$3,3,0)))

Thanks it
Works!