Search a range of cells in another tab and return a value

[andrebooyzen]

Hi Guys

I want to populate a cell with a value picked up from a matched value in another range of cells in a different tab. e.g the cell I want to populate is B2. The tab name to search is in B1. The value I want to search for is in cell A2. The range to search in the other tab is A1:C4. If it finds a match for A2 in the search range in the other tab (A1:C4) it should return the rightmost 3 characters - but only if the 2nd and 3rd last character in the searched cell is an "XM".

Cells I am wanting to populate....

A	B	C
	tab name	tab name
John	I WANT TO POPLULATE THIS CELL
Peter

This is what the other tab looks like. I want to search a range of cells (A1:C4) and if it finds a match for the value in A2 (John) then return last 3 characters in cell - only if the 2nd and 3rd last characters in the cell are "XM". In the range below it should search A1 to C4. It should find a match for "John" and if it also finds XM in the same cell then it should return the last 3 characters in the cell. In this case it should find a match in cell C2 and return "XMP".

A	B	C
0900 John Sheila	0930 Joe Peter	0820 Susan Ian
1100 Peter Andrew	1100 John Vic	1000 John Frank XMP
1200 Ian Sue	1220 Val Fiona	1500 Joseph Cal
1400 Andy Brian	1700 Bella Jacky	1600 Rochelle Burt

Any help would be apprecaited.

 

[andrebooyzen]

anyone able to help?? Thanks.

 

[Peter_SSs]

Give this a try. For the moment I have assumed at most one cell in the relevant sheet would meet the conditions.
I have changed the sample data a bit.

andrebooyzen.xlsm
	A	B	C
1	0900 John Sheila	0930 Joe Peter	0820 Susan Ian
2	1100 Peter Andrew AXM	1100 Sam Vic XMZ	1000 John Frank XMP
3
4
Sheet2

andrebooyzen.xlsm
	A	B
1		Sheet2
2	John	XMP
3	Peter
4	Vic	XMZ
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LET(c,TOCOL("|"&SUBSTITUTE(INDIRECT("'"&B$1&"'!A$1:C$4"),CHAR(10),"|")&"|"),LEFT(RIGHT(FILTER(c,ISNUMBER(SEARCH("|"&A2&"|",c))*(LEFT(RIGHT(c,4),2)="XM"),""),4),3))

 

[andrebooyzen]

Give this a try. For the moment I have assumed at most one cell in the relevant sheet would meet the conditions.
I have changed the sample data a bit.

andrebooyzen.xlsm
	A	B	C
1	0900 John Sheila	0930 Joe Peter	0820 Susan Ian
2	1100 Peter Andrew AXM	1100 Sam Vic XMZ	1000 John Frank XMP
3
4
Sheet2

andrebooyzen.xlsm
	A	B
1		Sheet2
2	John	XMP
3	Peter
4	Vic	XMZ
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LET(c,TOCOL("|"&SUBSTITUTE(INDIRECT("'"&B$1&"'!A$1:C$4"),CHAR(10),"|")&"|"),LEFT(RIGHT(FILTER(c,ISNUMBER(SEARCH("|"&A2&"|",c))*(LEFT(RIGHT(c,4),2)="XM"),""),4),3))

Thanks a lot Peter. Tried this and triple checked I have the formula correct....but I get a SPILL! error...
There could be more than one cell meeting the conditions, but I only need to pick up the first or last occurrence.

 

[Peter_SSs]

There could be more than one cell meeting the conditions,

That wasn't mentioned before so I didn't allow for it.
Try this

andrebooyzen.xlsm
	A	B	C
1	0900 John Sheila	0930 Joe Peter	0820 Susan Ian
2	1100 Peter Andrew AXM	1100 Sam John XMZ	1000 John Frank XMP
Sheet2

andrebooyzen.xlsm
	A	B
1		Sheet2
2	John	XMZ
3	Peter
4	Sam	XMZ
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=LET(c,TOCOL("|"&SUBSTITUTE(INDIRECT("'"&B$1&"'!A$1:C$4"),CHAR(10),"|")&"|"),INDEX(LEFT(RIGHT(FILTER(c,ISNUMBER(SEARCH("|"&A2&"|",c))*(LEFT(RIGHT(c,4),2)="XM"),""),4),3),1))

 

[andrebooyzen]

It works like a charm!!! Thanks so much Peter. Much appreciated.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.