Best options for Index match when both source and lookup have numbers & text

[CasualDabbler]

Good Afternoon everyone

My data contains numbers and text.
I.e. 13 & ABC1001
Depending on where I get my data from:-

• sometimes the lists are values, so numbers will be as number and text as text.
• sometimes the lists are text, so numbers will be as text and text as text. I.e. 13 may be listed as 0013
  • Is there a "Catch all" method which can lookup irrespective of data type in the source or the lookup table
• If it helps, my text numbers are formatted as "0000", and could be any number from 0001 to 9999

On a side note, I previously saw somewhere that you can incorporate - or -- in the formula which I seem to think helped?
If anyone can advise & explain this it would be appreciated

Book2
	A	B	C	D	E	F	G	H
3	Example of data as text	Return Value		Example of data as values	Return Value		Values to what to lookup
4	0038	A		38	A		0042	Should return B, when looking in either data set
5	0042	B		42	B		42	Should return B, when looking in either data set
6	0043	C		43	C		GAR1001	Should return H, when looking in either data set
7	1091	D		1091	D
8	1092	E		1092	E
9	1093	F		1093	F
10	ABC1001	G		DLD1001	G
11	DEF1001	H		GAR1001	H
12	HGI0005	I		HAR0005	I
13	HGI0042	J		HGI0042	J
14	HGI9992	K		HAR9992	K
15	HGI1150	L		IND1150	L
Sheet1

 

[Peter_SSs]

I'm not sure how GAR1001 could return H from the left data set?

But see if this helps.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

21 09 21.xlsm
	A	B	C	D	E	F	G	H	I
3	Example of data as text	Return Value		Example of data as values	Return Value		Values to what to lookup
4	0038	A		38	A		0042	B	B
5	0042	B		42	B		42	B	B
6	0043	C		43	C		GAR1001	#N/A	H
7	1091	D		1091	D
8	1092	E		1092	E
9	1093	F		1093	F
10	ABC1001	G		DLD1001	G
11	DEF1001	H		GAR1001	H
12	HGI0005	I		HAR0005	I
13	HGI0042	J		HGI0042	J
14	HGI9992	K		HAR9992	K
15	HGI1150	L		IND1150	L
Lookup

Cell Formulas
Range	Formula
H4:H6	H4	=INDEX(B$4:B$15,MATCH(TEXT(G4,"0000"),TEXT(A$4:A$15,"0000"),0))
I4:I6	I4	=INDEX(E$4:E$15,MATCH(TEXT(G4,"0000"),TEXT(D$4:D$15,"0000"),0))

 

[CasualDabbler]

Using office 2016 - profile updated
Sorry - had a senior moment with the example data, corrected below.
Tried your suggestion, but it returns N/A on all results, which is strange as the formula builder suggest its finding the correct result - Can see what your doing with it and that seems the right way to attack this,

Book1
	A	B	C	D	E	F	G	H	I
3	Example of data as text	Return Value		Example of data as values	Return Value		Values to what to lookup
4	0038	A		38	A		0042	#N/A	#N/A
5	0042	B		42	B		42	#N/A	#N/A
6	0043	C		43	C		DEF1001	#N/A	#N/A
7	1091	D		1091	D
8	1092	E		1092	E
9	1093	F		1093	F
10	ABC1001	G		ABC1001	G
11	DEF1001	H		DEF1001	H
12	HGI0005	I		HGI0005	I
13	HGI0042	J		HGI0042	J
14	HGI9992	K		HGI9992	K
15	HGI1150	L		HGI1150	L
Sheet2

Cell Formulas
Range	Formula
H4:H6	H4	=INDEX(B$4:B$15,MATCH(TEXT(G4,"0000"),TEXT(A$4:A$15,"0000"),0))
I4:I6	I4	=INDEX(E$4:E$15,MATCH(TEXT(G4,"0000"),TEXT(D$4:D$15,"0000"),0))

 

[Peter_SSs]

Try this: Select the formula cell and press F2 then re-confirm with Ctrl+Shift+Enter, not just Enter

Thanks for updating your profile.

 

[CasualDabbler]

Ahh, need to enter as an array - Thanks
If I have a lot of lookups (2,000), do you know if there is much processing different between entering a std Index match and one as an array
Wondering if its better to use an array or expend the effort cleaning the data (But I don't control the source, so have to do it every time I get a data update)

 

[Peter_SSs]

2,000 lookups is not that much. I'd say give it a go and see what happens.

 

[CasualDabbler]

ok - Thanks for your help