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

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" <Mparekh.25pk51_1144100704.1577@excelforum-nospam.com> wrote in
message news:Mparekh.25pk51_1144100704.1577@excelforum-nospam.com...
>
> 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
>

Thanks Peo Sjoblom! It worked...

Thanks again,
Mparekh