selecting data from a combination of columns & rows.
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:
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?
--
Browny
"Stefi" wrote:
=VLOOKUP(M12,OFFSET(AD2,0,MATCH(L12,$AD$2:$AH$2,0)-1,5,2),2,FALSE)
€žBrowny€ť ezt Ă*rta:
Hi Stefi
I have edited my table below to display the correct rows/columns. could you
redo the forumla?
please
--
Browny
"Stefi" wrote:
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:
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
2 B&F PM QA
3 .5 door 4.4 .5 door 3.4 1 door 2.4
4 1 door 4.4 1 door 3.4 2 door 2.4
5 2 door 4.4 2 door 3.4 air curtain 6.4
6 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...
L M O
Call Type Product Points
12 B&F 1 door 4.4
13 QA 2 door 2.4
14 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.
--
Browny
|