Help needed for Nested IF VLOOKUP issues

[Sax_Guy_2020]

Greetings Excel users,

I am current working with two data tables that contain similar "Document Number" values. These values are in single columns on different Worksheets.

One table presents the data correctly, while the other does not (prefixes or suffixes are sometimes absent). I created 3 additional columns to solve this issue using the CONCATENATE function.

I want to show the correct matching value based on VLOOKUPs using the original data table and new columns as criteria (one the first worksheet, against the second data table, on the second worksheet).

The Formula I am using only partially works, returning a #N/A error sometimes instead of returning the matching value.

The original data is found in Column E of the first worksheet. The new columns I created using the CONCATENATE function are in columns M:O. The second table I want to match against is on the second worksheet in column B. The formula is on the first worksheet in column I:

=IF(E3=VLOOKUP(Sheet1!E3,'Data Source 2'!$B$3:$B$6,1,FALSE),Sheet1!E3,IF(Sheet1!M3=VLOOKUP(Sheet1!M3,'Data Source 2'!$B$3:$B$6,1,FALSE),Sheet1!M3,IF(Sheet1!N3=VLOOKUP(Sheet1!N3,'Data Source 2'!$B$3:$B$6,1,FALSE),Sheet1!N3)))


I certainly appreciate your help!



Thank you

 

[vletm]

@Sax_Guy_2020
Could this MATCH?
(I cleaned some cells, but left those A, S & 001-, if You want to test another combinations.)

 

[Sax_Guy_2020]

Oh my gosh, thank you so much! That worked like a charm! I am extremely grateful! I am attempting to deconstruct your formula to better understand the how the IFERROR, INDEX, ampersand symbol, and MATCH functions work in harmony. Thank you so much again!