I want excel to choose 2nd,3rd... matches in VLOOKUP if the first match is picked

Hi;

I have an excel workbook consisting three sheets, and I am trying to make excel fill my lists with the data from the 3rd sheet if all the criterias are met. I somehow managed to do it, but vlookup always picks the first match. My list should not repeat itself, if a matching value picked before from the list, I want excel to choose the second matching option. Then if the second one is picked too, it should select the third one until no matches are left between given cell ranges.

I am uploading my excel file to show my problem.

In the cell "AW5", excel finds a match and copies the data i want. In "AW6" and "AW7", excel finds no matches, ok. ıt finds new matches and so on, but when it comes to cell "AW19", excel finds a match but it is the same first match with the "AW5" cell. what i want to achieve is for the "AW19" cell or cells which have the same situation is to make excel to select the 2nd option or so, and if there's no other match is left; it should leave the cell empty.

can anybody solve my problem or lead me in the way to achieve my goal?

Btw a reminder: the ";" seperator is working like "," in your formulas, caused by local language properties or something like that. when i use comma in a formula, excel gives an error.


Thank You;

Çağrı

the way I usually do that is add a helper column - something like =Countif($AW5:$AW$2000,$AW5)+Countif(Sheet2!$AW:$AW,$AW5)+Countif(Sheet3!...

(counting ranges after the current cell)

You can use the If(Countifs=1,$AW5) (where Countifs is the Countif($AW5:$AW$2000....)

I will give it a try and make you know if it's working for my situation. thanks.

So, what you suggest may be a step to solve my situation. I can count all the duplicate values in my list and see how many repeats was occured. For example, let's say that i found 3 repetative values in my list for a cell. it will write 3 to the related helper column so i will know that excel should select the second match for the second repeating value, and the third match for the third one. Ok, now i have a logic for the solution, but still having problems when trying to apply it

• I know that I somehow can make excel to pick 2nd, 3rd and nth values in vlookup. But i am kinda confused about how to do it with all these connected formulas that i used.
• The workbook i study on is huge. and it will grow in time. So i need an automated solution, which means if there are 5 duplicate values in my list, the formula should shift according to the progress. there are several possibilities for a cell; it may find no matches from other list and leave the result empty; it may find the first match and copy the data to my list, it may find a match but the first match was picked up so it copies the second match data, it may continue like that until no matches are left and excel should leave the cell empty because we ran out of matching criterias. but how can i do that?

.

I did it!!!

I am sharing the final product with you guys.

What i did was creating an unique id from the criterias i look for in both sheets that i need to match. AB column in the sheet "değişiklik listesi" and Y column in the sheet "main core" are the unique id lists that i mentioned.
At the AC column, i used vlookup to transfer the matches. With the idea from CRIMEDOG i counted how many duplicates vlookup picked as it always picks the first match(AD column in the sheet "değişiklik listesi")
And in the AF column's formulas, it picks the right match according to number of repeats of previous column.

Thanks CRIMEDOG, for the idea that leads me to the result.