selecting data from a combination of columns & rows.

[Browny]

I have a sheet that has 3 catagories & data listed columns beneath
(B&F,PM & QA), see below
AC AD AE AF AG AH AI
B&F PM QA
.5 door 4.4 .5 door 3.4 1 door 2.4
1 door 4.4 1 door 3.4 2 door 2.4
2 door 4.4 2 door 3.4 air curtain 6.4
3 door 4.4 3 door 3.4 other 4.4
i have created 2 dropdown lists which i select, see below
B&F, then select 1 door. excel should then display 4.4 OR
QA, then secect 2 door. excel should then display 2.4.etc...
A1 A2 A4
Call Type Product Points
B&F 1 door 4.4
QA 2 door 2.4
PM FCB 12.4
i'm thinking of a combination HLOOKUP, VLOOKUP & Filtering
Can anyone provide a forumla that gives me my required result?
Thank You in anticipation.

 

[Stefi]

Try this in cell under Points and fill it down as necessary:

=VLOOKUP(B2,OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2),2,FALSE)

In OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2) replace 5 by the real number
of rows of your 1st table!

Regards,
Stefi

„Browny†ezt írta:

 

[Browny]

Hi Stefi
I have edited my table below to display the correct rows/columns. could you
redo the forumla?
please

 

[Stefi]

=VLOOKUP(M12,OFFSET(AD2,0,MATCH(L12,$AD$2:$AH$2,0)-1,5,2),2,FALSE)


„Browny†ezt írta:

 

[Browny]

Hi Stefi
Thanks for that, i have given the wrong impression.
from the table of data i want to be able to select B&F, PM or QA, then any
of the items below and display the joining points.
i think the forumla selects B&F, QA only?

 

[Stefi]

I checked again, the formula works if tables are located exactly in ranges
you specified. Maybe you are mislead by #NA error in row of PM FCB. FCB
doesn't exist in column AF, therefore the formula returns #NA.

Regards,
Stefi

„Browny†ezt írta: