lookup

[Jayne]

Basically I want to use both horizantal and vertical
criteria to lookup in a table i.e. match the horizontal
criteria and the vertical criteria and return the value
of the cell

e.g. horizontally look up "Dick" and vertically look
up "Mar" and return the answer 8

A B C D
1 Tom Dick Harry
2 Jan 1 2 3
3 Feb 4 5 6
4 Mar 7 8 9

Can anyone help please

Thanks, Jayne

 

[Max]

One way ..

Assuming the table below is in Sheet1, A14

A B C D
1 Tom Dick Harry
2 Jan 1 2 3
3 Feb 4 5 6
4 Mar 7 8 9

and assuming the months in A2:A4 are *text*,
not dates formatted to display as "mmm"

In Sheet2
-------------
Assuming the months ("Jan", "Feb", etc) are listed in A2 down,
names (Tom, Dick, etc) are in B2 down

Put in C2:

=IF(OR(ISNA(MATCH(TRIM(A2),Sheet1!$A$1:$A$4,0)),ISNA(MATCH(TRIM(B2),Sheet1!$
A$1:$D$1,0))),"",OFFSET(Sheet1!$A$1,MATCH(TRIM(A2),Sheet1!$A$1:$A$4,0)-1,MAT
CH(TRIM(B2),Sheet1!$A$1:$D$1,0)-1))

Copy C2 down

Col C will return the intersecting values from the table in Sheet1
for the months / names in cols A and B

Unmatched cases will return blanks ""

 

[Jayne]

Thanks Max, using ideas in your formulae I've found
another way:-

=VLOOKUP(A8,$A$1:$D$5,MATCH($B$6,$A$1:$D$1,0),FALSE)

Where cell B6 contains the horizantal data I want to look
up and A8 the vertical data.

I've spent half the morning looking for a formulae that
does what "match" does so really, thank you.

 

[Frank Kabel]

Hi
try
=INDEX(A110,MATCH("Mar",A1:A10,0),(MATCH("Dick",A11,0))

 

[Max]

Pleased to hear that, Jayne !
Thanks for the feedback and your insights

 

[Max]

=INDEX(A110,MATCH("Mar",A1:A10,0),(MATCH("Dick",A11,0))

Think you've got one parens too many, Frank <g>

Typo corrected:
=INDEX(A110,MATCH("Mar",A1:A10,0),MATCH("Dick",A11,0))

 

[Frank Kabel]

Hi Max
thanks for the correction

-----Original Message-----
("Dick",A11,0))

Think you've got one parens too many, Frank <g>

Typo corrected:
=INDEX(A110,MATCH("Mar",A1:A10,0),MATCH("Dick",A11,0))