Match and Lookup

[Biocellguy]

I would like to have excel determine if a number in one cell is equal to a
value in another cell. If it finds a match then for it to display the value
found in a corresponding row to the match. If there is no match, then it
should display either a blank or an “x.â€

I tried the LOOKUP function, but the problem is if there is no match LOOKUP
displays a value close to my inquiry as LOOKUP matches the largest value in
lookup_vector.


For example:
A B C D E F
1 7 14 21 42
2 0.1 0.2 0.3 0.4
3
4 14 20 7 21 42 50

In row 5 I want it to determine IF there is a match between A4 (B4,C4, etc.)
and one of the numbers in A1:F1. If there is, I want it to display the
corresponding number from row 2.

When I try LOOKUP (=LOOKUP(A4,$A1:$F1,$A2:$F2)) I get the following:
4 14 20 7 21 42 50
5 0.2 0.2 0.1 0.3 0.4 0.4

The following is how I would like row 5 to look:
4 14 20 7 21 42 50
5 0.2 x 0.1 0.3 0.4 x

Thank you a lot!

 

[Pete_UK]

Try this instead in A5:

=IF(ISNA(HLOOKUP(A4,$A$1:$F$2,2,0)),"x",HLOOKUP(A4,$A$1:$F$2,2,0))

then copy across. This will return "x" if there is not an exact match.

Hope this helps.

Pete