Match any value in row array to column headings

Hi,

I am struggling to find a formula that will approximately match all of the data in column b:d to the headings in e1:f1 and return "X" in the vertex. Attached is a simple example. I actually have 6 "forum" columns and more than 20 column headings to match (e.g. "abc").

a1:a4 = name/contact
b1:b4 = forum(1)
c1:c4= forum (2)
d1:d4 = forum (3)
e1 = "abc"
e2 = "def"

The closest I got to the formula is for 1 x "forum" only.
=if(sblank(a2,"",if(isna(match("*"&right(a2,5)&"*",e1,0),"",(match("*"(a2,5)&"*",e1,0))))

I am using microsoft excel 2003

Help!

thanks for the quick reply. I tried:

=IF(A2="","", IF(OR(ISNUMBER(SEARCH(E$1, $B2:$D2))), "x", ""))

it worked for cell e2, however when I copy the formula across, it does not recognise a match with f1.
In other words, the formula worked for "jane"'s data, not for "julie"'s data - i.e. F4. Help!

Oops - sorry. You are right, it's the second code:

| X | |
| | X |
| X | X |

Originally Posted by Cheeky Charlie

I think you meant:
=IF(ISBLANK(A2),"",IF(ISNA(MATCH("*"&RIGHT(A2,5)&"*",E1,0)),"",MATCH("*"&RIGHT(A2,5)&"*",E1,0)))

Of which the real brain of the formula is:
MATCH("*"&RIGHT(A2,5)&"*",E1,0)

Your example doesn't make sense to me though, are you expecting to find the names in column A in the top row? Also, did you mean:

Please Login or Register  to view this content.

or should it have been:

Please Login or Register  to view this content.

If it's the first I think I need a bit more help with the logic...

Originally Posted by JBeaucaire

Place the following array formula into E2, then confirm it by pressing CTRL-SHIFT-ENTER

=IF(A2="","", IF(OR(ISNUMBER(SEARCH(E$1, $B2:$D2))), "x", ""))


Copy that cell to the right. Then copy the pair of cells downward.

Hi Jerry,

Your formula was helpful. I've modified the formula to below - can you suggest how i can match "approximate" matches?

Formula so far is:
=if(b2="","",if(or(isnumber(search(e1,b2)),(isnumber(search(e1,c2))),"x",""))

Thanks for trying, your formula doesn't seem to work on excel 97.

Hi,

I've attached a modified spreadsheet to indicate what I am trying to achieve. This might be a clearer example.