Search cell return value from another cell

tlynn

Board Regular
Joined
Aug 28, 2006
Messages
137
Office Version
  1. 365
Platform
  1. Windows
On one sheet (sheet1) I've got a number in first column. That number is buried in the third column on sheet2 among the multiple items listed.
On the other sheet (sheet2), there are three columns - in the third column, each cell has multiple items listed.

I need to search sheet2 (the whole third column) for the number listed in the one column on sheet1 then return the value from sheet2 in the first column.

SearchTest_12.05.2022.xlsx
ABC
1NameLocationDescription
2BillxyzOptiplex 7040; PCN 1234567; warranty ab123cd; location xyz
3TomzyxOptiplex 7040; PCN 7654321; warranty ab123cd; location xyz
4
5
6
7
8
9
10
Sheet2


SearchTest_12.05.2022.xlsx
ABCDE
1Scan
21234567
37654321
4
5
6
7
Sheet1


Basically, I need to search and find from sheet1 (cell A2) and find it on sheet2 in column c and return the name on sheet2-column a, to sheet1-cell c2. So, in this example, if I search for cell A2 on Sheet1 (1234567) I should get "Bill" from Sheet2.

I am not sure I have explained this good enough...but, thanks for your time.
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I had tried VLOOKUP in the past and just couldn't get it to work right.

Using this on sheet1: =VLOOKUP("*"&A2&"*",Sheet2!A3:C176,1,FALSE)

With it set up that way, it returns everything in the cell (in this example it returns "Optioplex 7040; PCN 1234567; warranty ab123cd; location xyz") - I just need it to return the contents of cell A2 of the row that the "1234567" is found in - so in this example it should return "Bill".
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
It is o365 (Version 2202 (Build 14931.20806)) - I have updated my information as per your suggestion.

Thanks.
 
Upvote 0
Thanks for that.
How about
Excel Formula:
=FILTER(Sheet2!A2:A100,ISNUMBER(SEARCH(A2,Sheet2!C2:C100)))
 
Upvote 0
That works!

What would I need to do if it cannot find the number in A2 on Sheet2, but have another sheet (let's call it sheet3) it should check as well?

I am assuming that I would need to use an IF statement...

Thanks for the guidance in this!
 
Upvote 0
How about
Excel Formula:
=FILTER(Sheet2!A2:A100,ISNUMBER(SEARCH(A2,Sheet2!C2:C100)),FILTER(Sheet3!A2:A100,ISNUMBER(SEARCH(A2,Sheet3!C2:C100))))
 
Upvote 0
How about
Excel Formula:
=FILTER(Sheet2!A2:A100,ISNUMBER(SEARCH(A2,Sheet2!C2:C100)),FILTER(Sheet3!A2:A100,ISNUMBER(SEARCH(A2,Sheet3!C2:C100))))
Rockstar! Excellent! Thank you so much!

Have a great day!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top