Vlookup

[excelesha]

Hello, I would like vlookup to return REG if the cell contains REG in it, or otherwise return blank cell if the cell does not contain REG, see below:

Thanks in adnance.

Sheet 1			Sheet 2
Col A	Col B		Col A	Col B
apple	abc		apple
orange	abc reg		orange	REG
peach	sell reg		peach	REG
mango	sell		mango
lime			lime
berry	ITS Reg		berry	REG

 

[AhoyNC]

If Col A on Sheet 2 will always be in the same order as Col A on sheet 1 try formula for example 1 (cell E3).
If Col A on sheet 2 can be in a different order try formula in example 2 (cell H3).
Change ranges to match your data and sheet names.

Book2
	A	B	C	D	E	F	G	H
1	Sheet 1			Sheet 2	EX-1		Sheet 2	EX-2
2	Col A	Col B		Col A	Col B		Col A	Col B
3	apple	abc		apple			orange	REG
4	orange	abc reg		orange	REG		apple
5	peach	sell reg		peach	REG		mango
6	mango	sell		mango			peach	REG
7	lime			lime			berry	REG
8	berry	ITS Reg		berry	REG		lime
9
Sheet1

Cell Formulas
Range	Formula
E3:E8	E3	=IF(ISNUMBER(SEARCH("reg",B3)),"REG","")
H3:H8	H3	=IF(ISNUMBER(SEARCH("reg",XLOOKUP(G3,$A$3:$A$8,$B$3:$B$8))),"REG","")

 

[excelesha]

If Col A on Sheet 2 will always be in the same order as Col A on sheet 1 try formula for example 1 (cell E3).
If Col A on sheet 2 can be in a different order try formula in example 2 (cell H3).
Change ranges to match your data and sheet names.

Book2
	A	B	C	D	E	F	G	H
1	Sheet 1			Sheet 2	EX-1		Sheet 2	EX-2
2	Col A	Col B		Col A	Col B		Col A	Col B
3	apple	abc		apple			orange	REG
4	orange	abc reg		orange	REG		apple
5	peach	sell reg		peach	REG		mango
6	mango	sell		mango			peach	REG
7	lime			lime			berry	REG
8	berry	ITS Reg		berry	REG		lime
9
Sheet1

Cell Formulas
Range	Formula
E3:E8	E3	=IF(ISNUMBER(SEARCH("reg",B3)),"REG","")
H3:H8	H3	=IF(ISNUMBER(SEARCH("reg",XLOOKUP(G3,$A$3:$A$8,$B$3:$B$8))),"REG","")

Thanks so much for the reponse.

Column A will be shuffled, it will not follow the same order......

 

[excelesha]

If Col A on Sheet 2 will always be in the same order as Col A on sheet 1 try formula for example 1 (cell E3).
If Col A on sheet 2 can be in a different order try formula in example 2 (cell H3).
Change ranges to match your data and sheet names.

Book2
	A	B	C	D	E	F	G	H
1	Sheet 1			Sheet 2	EX-1		Sheet 2	EX-2
2	Col A	Col B		Col A	Col B		Col A	Col B
3	apple	abc		apple			orange	REG
4	orange	abc reg		orange	REG		apple
5	peach	sell reg		peach	REG		mango
6	mango	sell		mango			peach	REG
7	lime			lime			berry	REG
8	berry	ITS Reg		berry	REG		lime
9
Sheet1

Cell Formulas
Range	Formula
E3:E8	E3	=IF(ISNUMBER(SEARCH("reg",B3)),"REG","")
H3:H8	H3	=IF(ISNUMBER(SEARCH("reg",XLOOKUP(G3,$A$3:$A$8,$B$3:$B$8))),"REG","")

the second solution works for shuffled data. Thank you so much.

 

[AhoyNC]

You're welcome. Thanks for the feedback.