Can you make VLOOKUPS conditional?

[torgerjl]

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?

[Bob Phillips]

=VLOOKUP(IF(FLAGfield=1,Difference,TotalCost),lookup_table,colnum,FALSE)

[torgerjl]

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)

[Bob Phillips]

Sorry, that doesn't mean a lot to me.

[torgerjl]

If I enter the formula as stated above I recieve a #NAME? error. I am having trouble correcting the array/lookup portion.

[torgerjl]

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))