How to force xlookup value even if the lookup value and lookup array is different format (one is text & one is number)

[fayez_MrExcel]

My lookup_value is in number format while my lookup_array is in text format, how can i avoid returning #N/A even if either lookup value and lookup array is in different format but the same number

Book5
	A	B	C	D
1	Employee Number			Match
2	11528		207970	#N/A
3	11649
4	11529
5	11525
6	1000407
7	207970
8	207972
9	207975
10	208114
11	11651
12	208022
13	208044
14	25185
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MATCH(C2,$A$2:$A$14,0)

 

[etaf]

how about something like
=IFERROR(MATCH(C2,$A$2:$A$14,0),IFERROR(MATCH(C2*1,$A$2:$A$14,0),MATCH(TEXT(C2,0),$A$2:$A$14,0)))

so if c2 matches format - you will get 6
if an error
assuming the lookup is text
*1 to change to a value
if the array is text - then change the lookup value to text

left justified is text
right is a number

Book1
	A	B	C	D
1	Employee Number			Match
2	11528	number text array	207970	6
3	11649	text and text array	207970	6
4	11529	text and number array	11529	3
5	11525			#N/A
6	1000407			#N/A
7	207970	changed to text in array		#N/A
8	207972
9	207975
10	208114
11	11651
12	208022
13	208044
14	25185
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IFERROR(MATCH(C2,$A$2:$A$14,0),IFERROR(MATCH(C2*1,$A$2:$A$14,0),MATCH(TEXT(C2,0),$A$2:$A$14,0)))

 

[Peter_SSs]

Not quite sure where you are headed as thread mentions XLOOKUP but your example used MATCH.

What about this?

22 09 25.xlsm
	A	B	C	D
1	Employee Number			Match
2	11528		207970	6
3	11649
4	11529
5	11525
6	1000407
7	207970
8	207972
9	207975
10	208114
11	11651
12	208022
13	208044
Mixed Values

Cell Formulas
Range	Formula
D2	D2	=MATCH(C2&"",$A$2:$A$14&"",0)

 

[fayez_MrExcel]

Sorry for the confusion. Im actually doing the xlookup.

Book5
	A	B	C	D
1	Employee Number			Match
2	11528	Hris	207970	#N/A
3	11649	SFSupport
4	11529	Security
5	11525	ITHELP
6	1000407	Anindita
7	207970	Admin
8	207972	Comp
9	207975	EC
10	208114	ExtensionServiceUser
11	11651	Abhishek
12	208022	PMGM
13	208044	PS
14	25185	sf
Sheet1

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(C2,$A$2:$A$14,$B$2:$B$14)

Not quite sure where you are headed as thread mentions XLOOKUP but your example used MATCH.

What about this?

22 09 25.xlsm
	A	B	C	D
1	Employee Number			Match
2	11528		207970	6
3	11649
4	11529
5	11525
6	1000407
7	207970
8	207972
9	207975
10	208114
11	11651
12	208022
13	208044
Mixed Values

Cell Formulas
Range	Formula
D2	D2	=MATCH(C2&"",$A$2:$A$14&"",0)

 

[Peter_SSs]

Im actually doing the xlookup.

Same concept

22 09 25.xlsm
	A	B	C	D
1	Employee Number			Match
2	11528	Hris	207970	Admin
3	11649	SFSupport
4	11529	Security
5	11525	ITHELP
6	1000407	Anindita
7	207970	Admin
8	207972	Comp
9	207975	EC
10	208114	ExtensionServiceUser
11	11651	Abhishek
12	208022	PMGM
13	208044	PS
14	25185	sf
Mixed Values

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(C2&"",$A$2:$A$14&"",$B$2:$B$14)

 

[fayez_MrExcel]

Same concept

22 09 25.xlsm
	A	B	C	D
1	Employee Number			Match
2	11528	Hris	207970	Admin
3	11649	SFSupport
4	11529	Security
5	11525	ITHELP
6	1000407	Anindita
7	207970	Admin
8	207972	Comp
9	207975	EC
10	208114	ExtensionServiceUser
11	11651	Abhishek
12	208022	PMGM
13	208044	PS
14	25185	sf
Mixed Values

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(C2&"",$A$2:$A$14&"",$B$2:$B$14)

Thank you...that works perfect

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[JV0710]

Hi All

Thanks very much Peter for your solution. How can I do the same but remove leading zeros from numbers in column A if there are any

Joe

 

[Peter_SSs]

Hi All

Thanks very much Peter for your solution. How can I do the same but remove leading zeros from numbers in column A if there are any

Joe

Looks like a bit of a different question. I suggest that you start a new thread to ask. Also best if you provide some varied sample data and the expected results with XL2BB in the new thread. You can put a link to this thread as background it you want.

 

[JV0710]

Hi All

Thanks very much Peter for your solution. How can I do the same but remove leading zeros from numbers in column A if there are any

Joe

Apologies . . . To be clear . . . . Do the same but take into account any leading Zeros in column A . . . I will Start a new thread