Using VLOOKUP and not only match on first value

Question

I'm trying to use VLOOKUP to match activities with product codes, but run into an issue since VLOOKUP always returns the first match. I did a mockup below to describe my issue. To the left, I have a table with activity names and product codes.

To the right, in column G, I want to, based on matching activity names in column F with activity names in column A, assign the activities product codes from column B.

When I use VLOOKUP, it only matches with the first activity name and give all the activities with the same name the same product codes. I need them to get different product codes even if they have the same name. I want my function to "take the next one" on the list, when the first one is taken.

Should I try to use another function to solve this? Is it impossible with VLOOKUP? My 'real' document has like 2000 rows, and the solutions I found on Youtube was not good to scale.

Thanks in advance. I'm new to here so if I should clarify my question in any way, feel free to tell me.

How are you getting the data in column F? This is a case for a Pivot Table. Or if VBA is allowed, I would give it a shot but I need to know the table name and/or if this is the only table on the worksheet, and if the data in columns A and B is values or formulas. If VBA is not allowed, feel free to ignore the request. I suppose the data in column A is not sorted in your real worksheet. — VBasic2008
Thanks a lot for responding @VBasic2008. The data in column F is copied from another table. Column A and B are values. Data in column A is not sorted. Basically what I'm going to do is to match the activities in F with codes in column G, then copy everything over column by column into a template and upload it into a system. I guess VBA could work for just 'creating' column G and then copy it over? I'm gonna redo the same type of process for several countries. — Soph
@Vbasic2008 some additional information. I have like 4 different documents with different data I need to match and this is just one of them. So ideally, if a Pivot Table could work, I might be able to create Pivot Tables for future matching as well. I'm not that good at Excel yet, but I'm a fast learner. — Soph
I use this to get the top 5 dealing with duplicates, so you can edit it to suit your needs and if it helps you give it a vote: stackoverflow.com/a/58640367/4961700 — Solar Mike
Or you might consider using indirect() with the row that vlookup finds plus 1 to continue to the next... — Solar Mike

Xlsx Xlsx · Accepted Answer · 2020-08-25T00:51:32

If the raw is around 2,000 rows, you can use a nested index match with helper columns.

Add a rank in column C with the formula =COUNTIF(A2:$A$2,A2)

Then apply the same ranking in your output part as well (Ensure Activity Name is sorted so that the formula works), Output rank formula =IF(J2=J1,I1+1,1)

Formula that lists out the Product Code {=INDEX($B$2:$B$3190,MATCH(I2,IF($A$2:$A$3190=J2,$C$2:$C$3190),0))}

This is an array formula, you get the curly brackets by hitting control+shift+Enter instead of just Enter upon keying in the formula

Using VLOOKUP and not only match on first value

2 Answers