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?

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.