Find A Match Formula

[Guest]

Here's my data table - Starts in A1 (Sheet1)

Type Code
9P170.00 RI
9P170.00 RI
9P160.00 RI
9P160.00 RI
9P150.00 SI
9P150.00 SI
9P140.00 RI
9P140.00 RI

I am trying to create this table - Starts in A1 (Sheet2)

Type Code
9P170.00 RI
9P160.00 RI
9P150.00 SI
9P140.00 RI


So. I am trying to find a formula for B2:B5 that will look at A2:A5 (Sheet2)
find the match in A2:A8 (Sheet 1) and if a match is found, return value in
B2:B8 (Sheet1). If no match is found, return "No Match".

Thank you in advance.

 

[Pete_UK]

Try this in B2 of Sheet2:

=IF(ISNA(VLOOKUP(A2,Sheet1!A$2:B$8,2,0)),"No match",
VLOOKUP(A2,Sheet1!A$2:B$8,2,0))

All one formula - I've split it to avoid awkward line-breaks. Copy
down into B3:B5.

Hope this helps.

Pete

 

[Guest]

thank You Pete. I tried this but did not work out. Perhaps because my data
table has dups or is not sorted correctly.

I think I need some sort of match/index type formula ?

 

[Pete_UK]

You can use INDEX/MATCH instead of VLOOKUP, but they will both work in
the same way - they will try to find a match in your table and if one
does not exist they will report an error. My formula traps this error
and tells you that you have no match.

In what way did it not work out? Did you get the message "No match",
or did you get results that you did not expect? If you do have
duplicates in your table then VLOOKUP (and MATCH if you use that) will
find the first match and return the corresponding value from that
first match, whereas you might expect the value from a different
match.

If you got "No match" then it means there are no matching values - as
you are looking for strings, you may have leading or trailing spaces
either in your table on Sheet1 or in the list in Sheet2.

Hope this helps further.

Pete