Can you make VLOOKUPS conditional?
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?
=VLOOKUP(IF(FLAGfield=1,Difference,TotalCost),lookup_table,colnum,FALSE)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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)
Last edited by torgerjl; 10-01-2008 at 05:29 AM.
Sorry, that doesn't mean a lot to me.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
If I enter the formula as stated above I recieve a #NAME? error. I am having trouble correcting the array/lookup portion.
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))
Posting Permissions
Reply With Quote
