INDEX-MATCH with LEFT 10 digits

[Morgan_T]

I am currently trying to index match with only the first 10 digits of the lookup value.

=index(R:R, MATCH(E2,AQ:AQ,0))
this returns my match if they are exact, the problem is I am working wit data that is not to be modified.

what I need (AQ:AQ) , what I need with extra string(E2), value from same row of match AQ:AQ ( R:R)

=INDEX(R:R, MATCH(LEFT($E2, 10)*1, AQ:AQ, 0))
returns #N/A and I know a match exists.

I also tried below just to drop the index to work and nothing pull a valid match.
=MATCH(LEFT(E2,10),AQ:AQ,0)

Please help!
Thanks,

Morgan

 

[steve the fish]

Put =LEFT(E2,10) in a cell. Copy it. Press CTRL-F. Paste it into the find what box. Click optionsand make sure match entire cell contents is checked. Highlight column AQ. Press find. Does if find anything?

 

[ekrause]

If I understand what you are asking for you just need to add a wildcard to the end of your match Lookup Value =INDEX(R:R, MATCH(LEFT($E2, 10)&"*", AQ:AQ, 0))

This method will only return the first value that meets the 10 digit string. For example if you have the following items: 1234567890A, 1234567890B, 1234567890C it will only match on the first listed value.

Let me know if that works.

 

[Morgan_T]

If I understand what you are asking for you just need to add a wildcard to the end of your match Lookup Value =INDEX(R:R, MATCH(LEFT($E2, 10)&"*", AQ:AQ, 0))

This method will only return the first value that meets the 10 digit string. For example if you have the following items: 1234567890A, 1234567890B, 1234567890C it will only match on the first listed value.

Let me know if that works.

This worked perfectly, I've never used he wild card. Thanks!