Index-Match from a range

M

Mparekh

Hello Everyone,

Am trying to figure out how to lookup a value through a range of cell
and seems this forum is my best bet. I have table in following format:

_colA_:::_ColB_ :::_ColC_
_pkt_ ::: _ordercount_ ::: __speed_
<4 ::: <20,000 ::: 7500
<4 ::: 20000-40000 :::8500
5-8 ::: <20000 ::: 9000
5-8 ::: 20000-40000 ::: 9500
... and so on and so forth

How do I write a formula such that for a given pkt and order coun
combination system would lookup associated speed. For e.g. for a 5 pk
job and 30,000 order count, the formula would retrieve 9500 and so o
and so forth. I guess I would have to use a combination of Index, Matc
and If statement but am not able to come up with a good formula for th
above scenario. Could anyone please help?

Thanks in advance,
mparek
 
P

Peo Sjoblom

You have the wrong layout which makes it very hard if not impossible to get
a valid result. If you changed it to

0 20000 40000
0 7500 8500
4 9000 9500
8
and so on


Assume the whole table would be A2:D5
with pkt going down in A staring with 0 in A3 and order count across in row
2 starting in B2 with 0

=INDEX(A2:D5,MATCH(5,A2:A5,1),MATCH(30000,A2:D2,1))


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
Top