using the find command across multiple columns

[Danny54]

Good Morning all;

I've been trying to create a solution that would do a lookup across multiple columns between to excel worksheets I have two sheets as listed here, where the data in sheet1 (column A) is looked up in sheet2 (column B and column C). If found by scanning data found in these columns(can be anywhere in the column), the value from (column A Sheet2) is returned to Sheet1 column B

any help would be appreciated greatly.

Thanks



Sheet1

Server	Return Value
Tom1
Tom2
Sally1
John3

Sheet2

User	Server1	Server2
-	Margret
Guest39	John	Tom2
Guest40	John3
Master1	Fred1	Mr. John3

Sheet1 after successfully running lookup vba code

Server	Return Value
Tom1
Tom2	Guest39
Sally1
John3	Master1

 

[Danny54]

trying to move forward with a solution, I inserted a helper column to be a new column A of sheet2 and filled it with =B1&" "&C1. this concatenated my search data into one cell
I then inserted =VLOOKUP("*"&A1&"*",Sheet2!A:B,2,FALSE) and copied down. This returned one of the matches, but not all. Do i need a array formula here in order to get all the columns found to be in one row?

 

[jtakw]

Hi,

In Sheet 2, you have "John3" in Both B & C columns, your expected result shows Master1, why? and why not Guest40?

 

[Danny54]

my bad, when scanning column B for John3 it would return Guest40 then when scanning for John3 in column C, a match would be made as well for Master1 since the string "Mr. John3" contains the string John3.

Net effect, for John3 in sheet1 after completion would say Guest40 Master1

 

[jtakw]

This would give you your desired results.
Since you have 365, there may be functions available for a simpler formula, I don't have 365:

Book1
	A	B
1	Server	Return Value
2	Tom1
3	Tom2	Guest39
4	Sally1
5	John3	Guest40 Master1
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=TRIM(IFERROR(LOOKUP(2,1/SEARCH(" "&A2&" "," "&Sheet2!B$2:B$10&" "),Sheet2!A$2:A$10),"")&" "&IFERROR(LOOKUP(2,1/SEARCH(" "&A2&" "," "&Sheet2!C$2:C$10&" "),Sheet2!A$2:A$10),""))

Book1
	A	B	C
1	User	Server1	Server2
2	-	Margret
3	Guest39	John	Tom2
4	Guest40	John3
5	Master1	Fred1	Mr. John3
6
7
8
9
10
Sheet2

 

[Danny54]

Thanks,
let me give this a shot.

Have a great weekend

 

[Danny54]

Thanks so much. This solution worked and was much easier than I thought. I was thinking it would be much harder than it really needed to be.

 

[jtakw]

You're welcome, thanks for the feedback.