Finding a value associated with a range

G

Guest

Hello. I am attempting to create a formula that searches through a range
of values to identify where a number falls within the range and then, once
identified, takes an associated value as a multiplier in the formula. Here is
the range data:

A B C D E
1 400 600 950 1250 1450
2 $15 $30 $45 $50 $55

Lookup value: 425

Based on these values, I need a formula to search a1 through e1 and
return the value in B2 ($30) associated with 600 (because 425 is over
400, but not greater than 600) and allow me to then multiply the lookup
value of 425 by the value returned (e.g., $30) giving me a result of $12,750.
It's tough even describing what I want, but hopefully someone understands
and can assist me ASAP. Thank you.
 
B

Biff

Hlookup won't work.

The OP seems to want the lowest value that is greater than the lookup value
if there's not an exact match.

Reverse the order of the table.

1450 1250 950 600 400
55 50 45 30 15


A5 = lookup value = 425

=IF(A5>A1,A2,INDEX(A2:E2,MATCH(A5,A1:E1,-1)))

Biff
 
B

Bernard Liengme

If you can put the values in descending order (row 1 has 1450, 1250...; row
2 has 55,50,...)
then this will give required result =INDEX(A2:E2,1,MATCH(A8,A1:E1,-1))
best wishes
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
 
G

Guest

Nick, thank you for the information. When I utilize HLOOKUP for some
reason it comes back with the value associated with 400 versus 600. In
reading about the formula it denotes that if it can't find an exact match
it will select the next highes value (which would be 600), which would be
what I want, but it doesn't seem to be working. Any thoughts?
 
P

Peo Sjoblom

This will work without having to reverse the order, with 425 in A5

=INDEX(A2:E2,MATCH(SMALL(A1:E1,COUNTIF(A1:E1,"<"&A5)+1),A1:E1,0))

hardcoded with 425 it would look like

=INDEX(A2:E2,MATCH(SMALL(A1:E1,COUNTIF(A1:E1,"<425")+1),A1:E1,0))

--
Regards,

Peo Sjoblom

(No private emails please)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the corresponding value in a range? 4
With F1 value, look at A, if match or partial match return B 5
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5
Finding NEXT to last, non zero numeric value in a row 3
Lookup an exact Value with a range 8
Locating values in a range and bringing up the corresponding price 3
average text value for a ranage of cells 2
hlookup value within a range 4

Top