Match cells in Column and Row and display data....

[coccio]

I want look through excel and find a code such as 3AA00 in a particular column and match that with an account # in a certain row and display that data in the cell that references those.

An example I have is


A2: A5 has my code
B1: E1 listed account #

100 200 300 400
3AA00 33 6 8 5
3AB00 45 0 8 0
3AE20 5 4 5 5
3AA00 7 5 5 6

Cell G1 look up condition 3AA00
Cell H1 lookup condition 100

I want to match G1 and H1 and display it in a ceratin cell

Such has A7

In this case it should display 33

[Aladin Akyurek]

On 2002-05-16 09:28, coccio wrote:
I want look through excel and find a code such as 3AA00 in a particular column and match that with an account # in a certain row and display that data in the cell that references those.

An example I have is


A2: A5 has my code
B1: E1 listed account #

100 200 300 400
3AA00 33 6 8 5
3AB00 45 0 8 0
3AE20 5 4 5 5
3AA00 7 5 5 6

Cell G1 look up condition 3AA00
Cell H1 lookup condition 100

I want to match G1 and H1 and display it in a ceratin cell

Such has A7

In this case it should display 33

Method 1 (my preference)

=OFFSET(A1,MATCH(G1,$A$2:$A$5,0),MATCH(H1,$B$1:$E$1,0))

Method 2

=INDEX(A1:E5,MATCH(G1,A:A,0),MATCH(H1,1:1,0))

Method 3

Use the intersection operator.

Aladin

[coccio]

Thanks for you help I have a follow up question if I may...

If lets say Cell H1 the look up condition is 555 something that is not listed in B1:E1

I get a #N/A

How do I remove that?

I believe with an If statement but I’m having difficulty.

I’m using Methdod one.

[Aladin Akyurek]

On 2002-05-16 12:13, coccio wrote:
Thanks for you help I have a follow up question if I may...

If lets say Cell H1 the look up condition is 555 something that is not listed in B1:E1

I get a #N/A

How do I remove that?

I believe with an If statement but I’m having difficulty.

I’m using Methdod one.

=IF(AND(COUNTIF($A$2:$A$5,G1),COUNTIF($B$1:$E$1,H1)),OFFSET(A1,MATCH(G1,$A$2:$A$5,0),MATCH(H1,$B$1:$E$1,0)),0)

will return 0 (last one, change it to whatever you wish) when either G1 or H1 or both are not available in the target range of interest.

Aladin