Index Match left of lookup and left of value

[RockandGrohl]

Hi guys

Comparing long chains of numbers, where say, the first 10 digits of the lookup value and the array they are stored in match, however the right side of the digits don't match for whatever stupid reason.


Anyway to do an index match that's like...

Index([answer column],match(left([Lookup value],10),left([lookup array],10),0))

?

Cheers!


For further clarification, the string of numbers is 17 digits long, but only the first 15 digits match. For some god-forsaken reason on one sheet they all end with a 0, and on the other it's a random digit.

 

[RockandGrohl]

If I can do something similar to this:

INDEX MATCH + LEFT Function

Hi, I'm trying to go from the top formula to the bottom one. I have to match H9 (which is a string of 8 characters) to column A which has the same set of information as in column H but with an added number at the end. So I need to be able to match H9 to the leftmost 8 characters in column A. I...

www.mrexcel.com

Where I lookup the first 14 digits, plus a wildcard, that would possibly work.

=INDEX('UHB BES Total Data 20-08-21.csv'!$I:$I,MATCH(LEFT($H2,14)&"*",'UHB BES Total Data 20-08-21.csv'!$AK:$AK,0))

However, this does not work.

 

[Peter_SSs]

Why not give us some sample data and expected results with XL2BB so that we can test?