How to us a Lookup within a Lookup Function?

  • Thread starter Thread starter noah
  • Start date Start date
N

noah

I am trying to set up a formula in a cell to lookup & reference an
amount of data within another sheet. The complication is that I need to
find a "commission rate" for a specific "product" by a specific
"account". I have a sheet set up with all the "rate" info called
"Rates” that is sorted by column with "accounts". And on each row for
that "account" I have a "commission rate" under numerous columns with
"products". Because basically, almost every "account" has a different
"rate" for each "product". And I need sheet1, with newly imputed info
to be able to find this rate. It sounds to me like I need it to hlookup
within a vlookup. But I don't know how to do that or if I even can.
Anyone with suggestions please.
 
Noah,

It would be easier if you posted a sample of the Rates sheet (NOT
an attachment just about 5 or 6 lines of text) but i'll assume it's like:

Account Product1 Product2 Product3 Product4 . . .
ABC123 34.50 22.75 31.22 44.66
ABC124 42.25 33.11 16.46 42.35
ABC125 34.46 21.00 11.75 56.87

This table would start in A1 on the rates page. You want to return
a price ie(For account ABC124 and Product2 return 33.11) then you
need a match and a vlookup like:

=VLOOKUP("ABC124",Rates!A1:E5,MATCH("Product2",Rates!A1:E1,0),FALSE)

you can exchange the "ABC124" and "Product2" for cell references

Dan E
 
Back
Top