Search cell return value from another cell

[tlynn]

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
	A	B	C
1	Name	Location	Description
2	Bill	xyz	Optiplex 7040; PCN 1234567; warranty ab123cd; location xyz
3	Tom	zyx	Optiplex 7040; PCN 7654321; warranty ab123cd; location xyz
4
5
6
7
8
9
10
Sheet2

SearchTest_12.05.2022.xlsx
	A	B	C	D	E
1	Scan
2	1234567
3	7654321
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.

 

[iggydarsa]

This might help

 

[tlynn]

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".

 

[Fluff]

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’)

 

[tlynn]

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.

 

[Fluff]

Thanks for that.
How about

=FILTER(Sheet2!A2:A100,ISNUMBER(SEARCH(A2,Sheet2!C2:C100)))

 

[tlynn]

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!

 

[Fluff]

How about

=FILTER(Sheet2!A2:A100,ISNUMBER(SEARCH(A2,Sheet2!C2:C100)),FILTER(Sheet3!A2:A100,ISNUMBER(SEARCH(A2,Sheet3!C2:C100))))

 

[tlynn]

How about

=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!

 

[Fluff]

You're welcome & thanks for the feedback.