PDA

View Full Version : Can you make VLOOKUPS conditional?



torgerjl
09-30-2008, 01:13 PM
I am trying to populate one spreadsheet from another spreadsheet and have a conidtion to which field to reference. For example, I have a flag field populated with a 1 for an error or 0 for no error. I need a vlookup that would look at the FLAG field and if it = 1.00 then use field "Difference" else "Total cost" Does this make sense or is this possible?

xld
09-30-2008, 02:32 PM
=VLOOKUP(IF(FLAGfield=1,Difference,TotalCost),lookup_table,colnum,FALSE)

torgerjl
10-01-2008, 05:08 AM
Thanks XLD. Can you help me with the array?
I have also added a product lookup. For example, if my FLAGfield is column 'T', Difference is 'S' and theTotalCost is 'P', the formula must first look up a product (eg, D1/Sheet1!A) ...
=VLOOKUP(D1,Sheet1!A*(IF(Sheet1!T=1,Sheet1!S,Sheet1!P))*16:19,FALSE)

xld
10-01-2008, 06:03 AM
Sorry, that doesn't mean a lot to me.

torgerjl
10-01-2008, 06:13 AM
If I enter the formula as stated above I recieve a #NAME? error. I am having trouble correcting the array/lookup portion.

torgerjl
10-01-2008, 10:06 AM
I'm coming back on this one in case anyone would use something similar...

The IF statement has to be moved to the outside. For example, if I look up the product ID and the FLAG equals 1 (T20) then use the 'Difference' field (S19) if not equal to 1 use the 'Total Cost' field (P16):

=IF(VLOOKUP(D53,Sheet1!A:T,20,FALSE)=1,VLOOKUP(D53,Sheet1!A:S,19,FALSE),VLO OKUP(D53,Sheet1!A:P,16,FALSE))