INDEX MATCH TWO COLUMNS

[tezza]

Hi all,

Take a look at these two examples:

Staff Record Work Phones to be updated 2023.xlsx
	N	O	P
1
2	CTC#	IMEI 1	IMEI 2
3		350073855391309	350073855391317
4		350073855387307	350073855387315
5		350073855387125	350073855387133
6		353956849674888	355913289674884
7		350073855387000	350073855387018
8		350073855385384	350073855385392
9		350073855384080	350073855384098
10		350073855387927	350073855387935
11		350073855388149	350073855388156
Phone List

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O3:O146	Expression	=B3<>""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
O3:P11	Text length	=15

Staff Record Work Phones to be updated 2023.xlsx
	A	B
1	Device name	IMEI
5	CTC 001	350073855387000
6	CTC 002	350073855387307
7	CTC 003	350073855390582
8	CTC 004	350073855387125
9	CTC 004	350073855388792
10	CTC 005	350073855390228
11	CTC 007	350073855396761
12	CTC 008	350073855397389
13	CTC 009	350073855391309
14	CTC 010	350073855385392
15	CTC 011	350073855397363
16	CTC 012	350073855386960
17	CTC 013	350073855391135
18	CTC 014	350073855393149
19	CTC 015	350073855383405
20	CTC 016	350073855391556
21	CTC 017	350073855391663
22	CTC 018	350073855388958
Worksheet

I'm trying to use the INDEX MATCH functions or check where the numbers in the first sheet Col O or Col P exists in the second table Col B and return Col A, but because of the second check in Col P, I simply can't get it to work.

A typical expected result would be:

CTC 001	350073855387000	350073855387018

As it found 350073855387000 from the top sheet in the bottom sheet at CTC 001 in Col A.

It doesn't have to be INDEX/MATCH to achieve the result, it's just where my head was logically going.

Can you help please?

 

[JV0710]

Hi

Try: In cell N3: =IFERROR(INDEX($B$5:$C$22,MATCH(O3,$C$5:$C$22,0),1),"")&" "&IFERROR(INDEX($B$5:$C$22,MATCH(P3,$C$5:$C$22,0),1),"")
and copy down

 

[tezza]

Hi

Try: In cell N3: =IFERROR(INDEX($B$5:$C$22,MATCH(O3,$C$5:$C$22,0),1),"")&" "&IFERROR(INDEX($B$5:$C$22,MATCH(P3,$C$5:$C$22,0),1),"")
and copy down

You are a legend, thank you.

 

[JV0710]

Welcome

 

[Peter_SSs]

You are a legend, thank you.

Are you sure that is what you want?
For every row where a device name is found that formula will have a space character after or before the device name in the result cell. If no device name is found it will put a space character rather than a null string ("") as the result.
Each of those issues could easily be remedied by wrapping the formula in a TRIM function

=TRIM(IFERROR(INDEX($B$5:$C$22,MATCH(O3,$C$5:$C$22,0),1),"")&" "&IFERROR(INDEX($B$5:$C$22,MATCH(P3,$C$5:$C$22,0),1),""))

or you could just use this shorter option

=IFERROR(INDEX(B$5:B$22,IFNA(MATCH(O3,C$5:C$22,0),MATCH(P3,C$5:C$22,0))),"")

 

[JV0710]

Thank you Peter

 

[tezza]

Are you sure that is what you want?
For every row where a device name is found that formula will have a space character after or before the device name in the result cell. If no device name is found it will put a space character rather than a null string ("") as the result.
Each of those issues could easily be remedied by wrapping the formula in a TRIM function

=TRIM(IFERROR(INDEX($B$5:$C$22,MATCH(O3,$C$5:$C$22,0),1),"")&" "&IFERROR(INDEX($B$5:$C$22,MATCH(P3,$C$5:$C$22,0),1),""))

or you could just use this shorter option

=IFERROR(INDEX(B$5:B$22,IFNA(MATCH(O3,C$5:C$22,0),MATCH(P3,C$5:C$22,0))),"")

I think I've modified it to my updated sheet but I'm getting blank cells now.

=IFERROR(INDEX(B$5:B$22,IFNA(MATCH(O3,C$5:C$22,0),MATCH(P3,C$5:C$22,0))),"")

=IFERROR(INDEX(Worksheet!$B$2:$C$110,IFNA(MATCH(P3,Worksheet!$C$2:$C$110,0),MATCH(Q3,Worksheet!$C$2:$C$110,0))),"")

 

[Peter_SSs]

I'm wondering if you have changed your column layout since post #1? Based on the layout in that first post I think this is what you would need.

tezza.xlsm
	A	B
1	Device name	IMEI
5	CTC 001	350073855387000
6	CTC 002	350073855387307
7	CTC 003	350073855390582
8	CTC 004	350073855387125
9	CTC 004	350073855388792
10	CTC 005	350073855390228
11	CTC 007	350073855396761
12	CTC 008	350073855397389
13	CTC 009	350073855391309
14	CTC 010	350073855385392
15	CTC 011	350073855397363
16	CTC 012	350073855386960
17	CTC 013	350073855391135
18	CTC 014	350073855393149
19	CTC 015	350073855383405
20	CTC 016	350073855391556
21	CTC 017	350073855391663
22	CTC 018	350073855388958
Worksheet

tezza.xlsm
	N	O	P
1
2	CTC#	IMEI 1	IMEI 2
3	CTC 009	350073855391309	350073855391317
4	CTC 002	350073855387307	350073855387315
5	CTC 004	350073855387125	350073855387133
6		353956849674888	355913289674884
7	CTC 001	350073855387000	350073855387018
8	CTC 010	350073855385384	350073855385392
9		350073855384080	350073855384098
10		350073855387927	350073855387935
11		350073855388149	350073855388156
Phone List

Cell Formulas
Range	Formula
N3:N11	N3	=IFERROR(INDEX(Worksheet!$A$2:$A$110,IFNA(MATCH(P3,Worksheet!$B$2:$B$110,0),MATCH(O3,Worksheet!$B$2:$B$110,0))),"")