Change formula based on results of vlookup

**hutch@edge.net** · 07-25-2008, 03:01 PM

Greetings, Gurus.

This one is giving me a headache. Here's the deal,... the formula below will lookup a value, (net weight), based on a part number. It is tweaked to show blank if the returned value is #N/A or 0.

=IF(OR($S$3="",ISNA(VLOOKUP($S$3,npa,12,FALSE))),"",IF(VLOOKUP($S$3,npa,12,FALSE)=0,"", VLOOKUP($S$3,npa,12,FALSE)))*2.2

The "*2.2" at the end is used to convert Kg to pounds. The problem is, some of the weights are in pounds, (from domestic suppliers), and some are in Kg, (from overseas suppliers). If I have a list of supplier numbers, and this number is captured in cell K31, is there anyway to lookup this number to determine if the supplier is domestic or overseas, and then apply the correct formula based on that result?

I have named a table containing all domestioc supplier numbers "domestic", and all overseas supplier numbers "overseas". I was hoping I could lookup the vendor number, (cell K31), from these tables, and then apply the formula based on which table it was found in.

Since I'm certain this was confusing, (i've even confused myself), I've attached a sample workbook. The two cells bordered in thich RED are the cells containing the values. The sheet is protected, but no password is used.

Thanks in advance for any help you can offer.

Hutch

**NBVC** · 07-25-2008, 03:18 PM

Try:

=IF(OR($S$3="",ISNA(VLOOKUP($S$3,npa,12,FALSE))),"",IF(VLOOKUP($S$3,npa,12,FALSE)=0,"", VLOOKUP($S$3,npa,12,FALSE)))*IF(ISNUMBER(MATCH(K31,INDEX(overseas,,1),0)),2.2,1)

**hutch@edge.net** · 07-25-2008, 03:42 PM

Thanks for the reply, NBVC.

Works great, the only issue is that if cell S3 is empty, then rather than Q28, (cell containing the formula), being empty it says #VALUE!.

Any suggestions?

**NBVC** · 07-25-2008, 03:56 PM

If you get an #NA result within an OR/AND statement, it screws things up...

Try:

=IF($S$3="","",IF(ISNA(VLOOKUP($S$3,npa,12,FALSE)),"",IF(VLOOKUP($S$3,npa,12,FALSE)=0,"",VLOOKUP($S$3,npa,12,FALSE))*IF(ISNUMBER(MATCH(K31,INDEX(overseas,,1),0)),2.2,1)))

**hutch@edge.net** · 07-25-2008, 04:18 PM

You are Awesome!!

That fixed me right up!!

**olasa** · 07-25-2008, 04:19 PM

One option is the combine the Vendor names list (Domestic and International) into one list. Then search the combined list for Domestic/International or Pound/Kg. The new formula would then be something like: If ("Kg",1,2.2)

I have put the new formula in the enclosed file, as an example.
I was not sure where the formula was going so I just typed it as text.

Hope this helped
Ola