Help with index and match

[mp_robinson_uk]

Hi All,

I have a spreadsheet that looks like the following

A B C D
1 2 1 3 3 INDEX(A1:D1,0,MATCH(E2,A2:D2,0),1) = D
2 4 1 3 1 INDEX(A1:D1,0,MATCH(E3,A3:D3,0),1) = C
4 2 3 4 4 INDEX(A1:D1,0,MATCH(E4,A4:D4,0),1) = A

where A,B,C,D could be any word in row 1 of the spreadsheet.

Thats fine but I would like something that gives

A B C D
1 2 1 3 3 D
2 4 1 3 1 C
4 2 3 4 4 A D

as the 4 in E4 matches A4(A) and D4(D)

It would be even better if it the matches where aligned i.e. the results looked
like (where the . represents an empty cell), but if this is too much work I would be very happy with just the above solution.

A B C D
1 2 1 3 3 . . D
2 4 1 3 1 . C .
4 2 3 4 4 A . D

any ideas how I can do this? :dunno

Thanks for your help. :hi:

Mick

[mp_robinson_uk]

Hi All,

Ok I figured it out I can just do something like

=IF(IF(ISERROR(MATCH(E2,A2:A2,0)),0,1),A1," ") 
=IF(IF(ISERROR(MATCH(E2,B2:B2,0)),0,1),B1," ")

etc

Thanks,

Mick