VLOOKUP: Search string longer than target string

1. VLOOKUP: Search string longer than target string

Hi,

Thank you in advance for the help!

In "Records" sheet, I have a list of units (Column A), and each unit has a corresponding score (Column B). In my actual document, there are 900+ units.

In Sheet2, I have some units for which I want to find the corresponding score. The problem is that the name captured on the "Records" sheet are shortforms, so they are shorter than the actual name, which is used in Sheet2. As a result, the normal VLOOKUP doesn't work for me. Is there a way to make it work?

[In the sample document, Sheet2 B2 should return '5', and Sheet2 B3 should return '7']

I hope this is clear. Thank you very much!

2. Re: VLOOKUP: Search string longer than target string

Try

in B2

=IF(A2="","",(INDEX(Records!\$B\$1:\$B\$7,SUMPRODUCT(--ISNUMBER(SEARCH(Records!\$A\$1:\$A\$7,Sheet2!A2))*ROW(\$A\$1:\$A\$7)))))

Copy down

Corrected: no need for "array entry" (thank you Tony Valko)

3. Re: VLOOKUP: Search string longer than target string

Hi,

I suggest a helper column in your Records sheet between your Name and Score column.

Use an INDEX MATCH formula to return "Actual" names from Sheet2.

In the Helper column use this array formula and copy down. Entered with CTRL/SHIFT/ENTER.

``Please Login or Register  to view this content.``
Now you can use a regular VLOOKUP in the Sheet2

Attached is a version of your file showing how this works.

Hope this makes sense and works for you.

Cheers

4. Re: VLOOKUP: Search string longer than target string

You can try this formula :
Formula:
`Please Login or Register  to view this content.`

5. Re: VLOOKUP: Search string longer than target string

Originally Posted by JohnTopley
=IF(A2="","",(INDEX(Records!\$B\$1:\$B\$7,SUMPRODUCT(--ISNUMBER(SEARCH(Records!\$A\$1:\$A\$7,Sheet2!A2))*ROW(\$A\$1:\$A\$7)))))

...confirmed by pressing CTRL+SHIFT+ENTER
No need for array entry. Normal enter will do.

6. Re: VLOOKUP: Search string longer than target string

Originally Posted by southward
Hi,

I suggest a helper column in your Records sheet between your Name and Score column.

Use an INDEX MATCH formula to return "Actual" names from Sheet2.

In the Helper column use this array formula and copy down. Entered with CTRL/SHIFT/ENTER.

``Please Login or Register  to view this content.``
Now you can use a regular VLOOKUP in the Sheet2

Attached is a version of your file showing how this works.

Hope this makes sense and works for you.

Cheers
I've gone along with this. Very helpful!

One issue I faced though. For some reason, in the helper column, the correct unit name did not appear. Can it be because some of the "shortform" names are not entirely shortforms? I have two examples below:

 Records Col A Sheet2 Col A The Nohumna Unit (NU) The Nohumna Unit, Sinpopine (NU) Likpo of Malme The Likpo of Malme

7. Re: VLOOKUP: Search string longer than target string

Testing Southward's it formula picks up the second instance, Likpo of Malme, without modification once the range is expanded. As to the first instance where the abbreviation is included in parentheses, the following modification to the array entered formula finds that one:
Formula:
`Please Login or Register  to view this content.`
Note: Assumes the data is appended to the "A" columns in the file attached to post #3.
If there are other types of anomalies in column A of the Records sheet then it might be worth reading the details of Fuzzy Lookup Add-In for Excel. Admittedly I have never used the add-in however the description of its abilities seems to be in line with your question.

8. Re: VLOOKUP: Search string longer than target string

Problem solved!

Thank you to everyone who chipped in. Much appreciated!

9. Re: VLOOKUP: Search string longer than target string

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)