Excel 2007 : Conditional Vlookup

**himaforum** · 12-18-2011, 05:28 PM

Hi,
I have a problem in conditional vlookup, I don't know If its can be solved in any other way.
I have 5 named list. 1 List is general name list of 4 other list. check the attachment.
A2 cell: 1 series selected from name list, A3 is indirect list of A2 (sub list). Conditional vlooup.jpg
The code will find, the value of A3 from the related Pig column.

**teylyn** · 12-18-2011, 05:43 PM

Hello,

the lookup table always starts in B9. I think you need to start the lookup table in the column that has the value in B2. This means that for each lookup the table will move one column to the left and you always return the fifth column.

=IF(A2=A9,VLOOKUP(B2,B9:I14,5,0),IF(A2=A10,VLOOKUP(B2,C9:I14,5,0),IF(A2=A11,VLOOKUP(B2,D9:I14,5,0),IF(A2=A12,VLOOKUP(B2,E9:I14,5,0),"NO"))))

see attached.

cheers,

**teylyn** · 12-18-2011, 05:49 PM

Be aware that you need absolute cell referencing if you want to copy the formula down.

=IF(A2=$A$9,VLOOKUP(B2,$B$9:$I$14,5,0),IF(A2=$A$10,VLOOKUP(B2,$C$9:$I$14,5,0),IF(A2=$A$11,VLOOKUP(B2,$D$9:$I$14,5,0),IF(A2=$A$12,VLOOKUP(B2,$E$9:$I$14,5,0),"NO"))))

This formula would also work

=VLOOKUP(B2,OFFSET($A$9:$E$14,0,MATCH(A2,SERILER,0)),5)