lookups for duplicates

[ermias]

Hello beautiful people,
I really need help on the following please
Basically there are two sheets that contain the same document number. The “Lookup Value” sheet contains all the information I need for the relevant document number which is “Type” and “No.”. But the second sheet (the “Result” sheet) does not contain “Type” and “No”. Therefore, I want to bring “Item” and “No.” from the “Lookup Value” sheet by matching the document number which is the same in both sheets.

THE PROBLEM IS:
The normal VLOOKUP, INDEXMATCH formulas can never do this because the whole file is really messed up and here is why:

1. There are so many duplicate document numbers in the “lookup sheet” with varying “Type” and “No.” so when you use VLOOKUP or INDEXMATCH it gives a very biased information
2. The number of document no. columns in both sheets does not match.

Note: Please refer the filtered sample sheet to see the requirement but just to remind you that there are so many different scenarios than what is shown on the filtered sample sheet.

After completion I need to check and validate the result because I have tried this like a 100 times but it seemed to work fine when in fact it gives a totally wrong information which is also why I say that the person who solve it correctly without any flow is a real genius!


I really have no idea how to solve this so I want to get the help of an expert.

Thank you so much for the help EXCEL FILE.xlsx

[AliGW]

Why have you posted in the VBA section? You don't mention VBA in your post - was this in error? If so, I'll move the thread for you.

[kersplash]

In your diagram on 'Filtered Sample' tab shows the table on the far left the same as the table on the far right.

So I don't understand the logic you are aiming for.

[AliGW]

Another duplicate closed.