Index-Match from a range
 

Hello Everyone,

Am trying to figure out how to lookup a value through a range of cells
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 count
combination system would lookup associated speed. For e.g. for a 5 pkt
job and 30,000 order count, the formula would retrieve 9500 and so on
and so forth. I guess I would have to use a combination of Index, Match
and If statement but am not able to come up with a good formula for the
above scenario. Could anyone please help?

Thanks in advance,
mparekh


--
Mparekh
------------------------------------------------------------------------
Mparekh's Profile: http://www.excelforum.com/member.php...o&userid=14999
View this thread: http://www.excelforum.com/showthread...hreadid=529400

Index-Match from a range
 

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




"Mparekh" wrote in
message ...

Hello Everyone,

Am trying to figure out how to lookup a value through a range of cells
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 count
combination system would lookup associated speed. For e.g. for a 5 pkt
job and 30,000 order count, the formula would retrieve 9500 and so on
and so forth. I guess I would have to use a combination of Index, Match
and If statement but am not able to come up with a good formula for the
above scenario. Could anyone please help?

Thanks in advance,
mparekh


--
Mparekh
------------------------------------------------------------------------
Mparekh's Profile:
http://www.excelforum.com/member.php...o&userid=14999
View this thread: http://www.excelforum.com/showthread...hreadid=529400

Index-Match from a range
 

Thanks Peo Sjoblom! It worked...

Thanks again,
Mparekh


--
Mparekh
------------------------------------------------------------------------
Mparekh's Profile: http://www.excelforum.com/member.php...o&userid=14999
View this thread: http://www.excelforum.com/showthread...hreadid=529400