Double Lookup

  • Thread starter Thread starter Jim Palmer
  • Start date Start date
J

Jim Palmer

I need some assistance with a lookup.

In the following example

Vendor Plateau Percentage
8976 0 0.00%
8976 200,000 0.50%
8976 500,000 0.75%
8976 750,000 1.00%
8976 1,000,000 1.50%
9540 0 0.00%
9540 200,000 0.50%
9540 500,000 0.75%
9540 750,000 1.00%
9540 1,000,000 1.50%
9795 0 0.00%
9795 200,000 0.50%
9795 500,000 0.75%
9795 750,000 1.00%

If vendor number is9540 and amount is $375,000 I want to return 0.50%
(that is we bought more than $200,000 but less than $500,000)

That is, I would like to vary the range that vlookup refers to. I ca
use match to determine the first occurence of 9540 is on row 6 and th
last is on row 10. I'd like to use that information to tell th
vlookup to look for 375,000 in the range c 6 to d 10.

Note the number of plateaus will vary. One vendor may have only 2 o
three rows while another may have six or eight.

Any assistance would be appreciated.

Best regards

Jim Palme
 
Jim,

If your table starts in row 1 and is in columns A:C, and you have 375000 in
cell D1, and 9540 in cell E1, then this formula will return the 0.50%:

=VLOOKUP(D1,OFFSET(A1,MATCH(E1,A:A,FALSE)-1,1,COUNTIF(A:A,E1),2),2)

Format the cell with the formula as percent.

P.S. Can I get a signed baseball cap?
(Sorry, I used to be a big Oriole fan....)

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie

I've been working on combining match and offset as well. I neve
thought of using Countif. I found the number of rows by using Matc
(E1+1) to find the last row. Your approach is easier to follow.

Jim

PS - I can't spare any baseball caps, need them to prevent sunburn, ho
about some jockey briefs
 
i prefer to use the INDEX function with two embedded MATCH functions, one
that matches the row, the other the column. Then the whole formula returns
the value at their intersection.


Bernie Deitrick said:
Jim,

If your table starts in row 1 and is in columns A:C, and you have 375000 in
cell D1, and 9540 in cell E1, then this formula will return the 0.50%:

=VLOOKUP(D1,OFFSET(A1,MATCH(E1,A:A,FALSE)-1,1,COUNTIF(A:A,E1),2),2)

Format the cell with the formula as percent.

P.S. Can I get a signed baseball cap?
(Sorry, I used to be a big Oriole fan....)

HTH,
Bernie
MS Excel MVP
Click to expand...
 
forget about my previous post, i missread your question

James Ruppert said:
i prefer to use the INDEX function with two embedded MATCH functions, one
that matches the row, the other the column. Then the whole formula returns
the value at their intersection.
Click to expand...
 
I doubt that would work for this situation..

--

Regards,

Peo Sjoblom

James Ruppert said:
i prefer to use the INDEX function with two embedded MATCH functions, one
that matches the row, the other the column. Then the whole formula returns
the value at their intersection.
Click to expand...
 
Back
Top