how to bring all vlookup returns even with duplicate vlookup search keys

**NYC4LIFE** · 06-06-2013, 04:34 PM

Hello,

I am doing a Vlookup search across data that contains several parameters. I have created a unique key to do the vlookup against, using the concatenation of say A1 and B1. However, the problem is that there are duplicate keys using this format, but the rest of the parameters can be different (cells C1 to ZZ1, etc). so when i do a vlookup on my cell, and search to pull in what column C has, only the first match is returned.

Is there a way to differentiate that in the Vlookup so if my concatenation of A1B1 brings back C1, I want A2B2 to bring back C2, even if A1B1 = A2B2.

**Harribone** · 06-06-2013, 04:44 PM

Say you have aCol A and Col B with your values. In Col C you have done your join =A2&B2 to create your key but as you say this will not be unique as it stands. In Col D you can then create a unique identifier by entering =C2&COUNTIF($C$1:C1,C2) and dragging down.

So if you have "EXCEL" and "FORUM" in A2 and B2 for example and the same again in A3 and B3, C2 and C3 will display "EXCELFORUM". In col D though you will have "EXCELFORUM0" and "EXCELFORUM1".

This will then be you unique identifier for a vlookup. Note you will need to set up your lookup table in a similar manner also so this works.

**BB1972** · 06-06-2013, 04:49 PM

Originally Posted by NYC4LIFE

... I have created a unique key to do the vlookup against, using the concatenation of say A1 and B1. However, the problem is that there are duplicate keys using this format...

? So.... they're NOT unique? Can you concatenate other fields, in order to make keys which are actually unique?

**Harribone** · 06-06-2013, 04:53 PM

Or improving on my solution have your first join =A1&B1 then below in the next cell enter =A2&B2&COUNTIF($C$1:C1,A2&B2) and drag down so you don't need an extra column (this formula assumes you are in column C)