IF and VLookUp Formula, Need Assistance in correct Formula

[Buzz1423]

Good Afternoon fellow members,

Yes, I'm new to this forum but I could greatly use some assistance. I'm using Excel to create a a worksheet that will read, if G14 reads Silver (G14 is a dropdown box that a user can select three options - Gold, Silver, Brass), then B21 will be read and then match with a price in another sheet.

I've used this formula, =IF(B21="","",(VLOOKUP(B21,'2015PriceList'!B$1:F$1027,3,FALSE)) with worksheets where I just have 1 column of prices. But now I would like for it to read G14 first for (Gold, Silver, or Brass), then read B21 for the model number and then match up the correct price.

In the formula above I know the 3 refers to which column reads to produce the price, therefore in my multiple price sheet 3 refers to Gold, 4 refers to Silver, and 5 refers to Brass pricing.

I've started with this =IF(G14=SILVER,IF(B19="","",(VLOOKUP(B19,'2015NEMOArmsPriceList'!A$1:F$1027,5,FALSE))) but I get the #NAME? error.

Can I lump all this into one forumla? I thought I could nest this many things into an IF formula, but I don't know how to correct write it.

Any assistance would be greatly appreciated, trying to make my self employed business more efficient. I can create 3 separate worksheets, but I'm almost positive I can do this in one.

Thank you for your time!
Buzz1423

[gmr4evr1]

Not sure if this resolves everything, but, try putting SILVER in quotes so it reads =IF(G14="SILVER".... It also looks like you might be missing a ) at the end of the formula.

[Richard Buttrey]

Hi,

There are no doubt a few ways of doing this. An INDEX(lookup_range,MATCH(row_no),MATCH(col_no)) formula is one, but using your existing VLOOKUP create a helper cell and in that cell enter

Please Login or Register  to view this content.

This will return either 3, 4 or 5 depending on G14. Now in your VLOOKUP instead of the '3' you currently have enter the helper cell address instead.

[Buzz1423]

I greatly appreciate the help, the "Silver" corrected the issue and allowed me to finish the formula usingnesting IF statements. I may try the other suggestion in the future.

Thanks again,
Buzz1423

[gmr4evr1]

You're welcome Buzz, glad it worked for you. Can you help us out and mark this thread as SOLVED (under Thread Tools of your original post)...please? Thank you.