Multiple IF's and Lookups

**xbitterflyx** · 09-19-2011, 07:38 AM

Ok so I'm doing some excel invoices and can't for the life of me remember how to do what I need it to do.

Since we will have a few users it needs to be as simple as possible so we are using drop down boxes, one box with the code for the equipment being sold and another for for what discount section they are in

Now I have 4 other tabs with the pricing for these discounts and I want it to lookup for example
Discount code is f1 then go to the tab labelled f1 and produce the information for the equipment code (ex gst, gst, discounts etc) that is in the second drop down box

Someone suggested multiple if statements with a vlookup both of which I can do seperately but just can't make it work together

Thanks!

**Wobbles** · 09-19-2011, 07:56 AM

Originally Posted by xbitterflyx

Now I have 4 other tabs with the pricing for these discounts and I want it to lookup for example
Discount code is f1 then go to the tab labelled f1 and produce the information for the equipment code (ex gst, gst, discounts etc) that is in the second drop down box

Im no expert but would it be easier to put the discounts all on the same sheets but just in different columns and then change the lookup return column accordingly ?

**NBVC** · 09-19-2011, 07:57 AM

For 4 tabs, a nested VLOOKUP would probably be ok.

You, can however use 1 VLOOKUP with an INDIRECT reference to the appropriate sheet.

eg.

=VLOOKUP(A1,INDIRECT("'"&B1&"'!A1:B100"),2,FALSE)

This looks in range A1:A100 in the sheet named in Cell B1 of the active sheet. It looks for the item in A1 of the active sheet in column A and returns the item in column B of the referenced sheetB