# Lookup or find in excel help

Solved/Closed
Paul - Dec 13, 2010 at 07:35 PM
Paul - Dec 14, 2010 at 06:58 AM
Hello,

I have 2 data sheets sheet one contains my Master list of items by number
Row 1 Column 1: 453658
Row 2 Column 1: 839565
Etc
my second sheet has the information I want to get to:

Row 1 Column 1: Gloves 453658A Black , Row 1 Column 2, \$4.99
Row 2 Column 1: hat 839565 Pink , Row 2 Column 2, \$8.99
etc

I want to search for 453658 and it returns me the \$4.99 ignoring the rest of the information in that box so I end up with
453658 \$4.99

The closest I have got is using a lookup formula but this only works if the data in both column 1's are exactly the same.

=VLOOKUP(A1,Sheet2!\$A\$1:\$B\$50000,2,FALSE)

Paul
Related:

## 2 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 14, 2010 at 05:06 AM
in the first sheet (master sheet) in row 1 column 2 (that is cell B1) copy this formula

=VLOOKUP("*"&A1&"*",Sheet2!\$A\$1:\$B\$100,2,0)

you can copy B1 down

I assume there are less than 100 rows.
Thanks that has worked perfectly !!!

Paul