Formula help required!!!

[CADmanJP]

I have tried in vain to complete a correct formula for the following:

-----------40x-----43x-----45x-----49x
Group 1......10........20........30........40
Group 2......12........22........32........42
Group 3......14........24........34........44

I will manually type into Cell A1 the group I am using e.g. Group 2 and
into cell A2 the number x e.g. 43x.

Now in cell B1 I want the formula to return the answer 22.

Can anyone point me in the right direction and save me pulling my hair
out???

Thanks


--
CADmanJP

[davesexcel]

use this example I posted in your original post



40 43 45 49
Group 1 10 20 30 40
Group 2 12 22 32 42
Group 3 14 24 34 44

Group 1 =MATCH(A6,A1:A4) 45 =HLOOKUP(C6,B1:E4,B6)

A6 has the dropdown menu
B6 has the Match formula
C6 has the drop down menu for the top row
D6 has the final result, the Hlookup formula


40 43 45 49
Group 1 10 20 30 40
Group 2 12 22 32 42
Group 3 14 24 34 44

Group 1 2 45 30

[Peo Sjoblom]

Assume your table are in F1:J4 with Group1 starting in F2 going down to F4
and 40x in G1 going across to J1
then this formula will give you 22 if A1 is Group 2 and A2 is 43x

=INDEX(F1:J4,MATCH(A1,F1:F4,0),MATCH(A2,F1:J1,0))

for a generic formula that uses a named table see here

http://www.contextures.com/xlFunctio...ml#IndexMatch3

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"CADmanJP" <[email protected]> wrote in message
news:[email protected]...
>
> I have tried in vain to complete a correct formula for the following:
>
> -----------40x-----43x-----45x-----49x
> Group 1......10........20........30........40
> Group 2......12........22........32........42
> Group 3......14........24........34........44
>
> I will manually type into Cell A1 the group I am using e.g. Group 2 and
> into cell A2 the number x e.g. 43x.
>
> Now in cell B1 I want the formula to return the answer 22.
>
> Can anyone point me in the right direction and save me pulling my hair
> out???
>
> Thanks
>
>
> --
> CADmanJP