Vlookup- multiple results for same criteria. how to solve issue??

[imadhasan]

Hi there,

So I am using a concatenated vlookup of some information on a table to reference back to a table on a separate sheet. The table is obtaining multiple training completion dates in different columns. I concatenated the training name with the unique personid so on our vlookup table, the persons training date show up under the right column.

This is working well except when a person has done training multiple times at different dates. In this case, there are 2 dates with the same reference (=vlookup(concatenate(personid, training name).

I want to be able to pick up the smaller date as I want to see if he completed the training in a timely manner (the retaking of the training doesn't concern me).

Any solutions on how I can achieve this?

If I have not made myself clear, I can share a portion of my spreadsheet to illustrate my issue.

Thanks,
Imad.

[Hercules1946]

Hi
Vlookup will return the first matching entry it finds, so if you can organise your table so that duplicates are sorted in ascending date order that should work. If you can't do that, then you could try using the Index function as an Array Formula. Its difficult to construct these as a working example without relevant data, so if you decide to try Index and you need help with it, a SMALL sample of your data, including expected results would help. Please also ensure that any sensitive information is removed.

[imadhasan]

Hi Hercules,

I have shared the spreadsheet with extra info removed. I apologize for the messiness but removing info and adjusting can be time consuming!

Anyways the 4-6-2016 sheet is where I am testing the info. The vlookup is incorporated with additional conditions, so I don't know if this will mess with your index plan.

If you need me to explain whats going on, let me know.

[imadhasan]

P.S: I tried sorting the data in 'data15', and 'data16' sheets. It doesn't change my vlookup values.

[imadhasan]

In my case where multiple results show up for my vlookup, is it possible that I use a small function within the vlookup to pick the older date?

Could someone incorporate the small function within my formula? (I assume you insert it somewhere around the 'DATA16' part)

=IF($K12<'4-6-2016'!$R$4,"n/a",IFERROR(VLOOKUP(CONCATENATE($A12,L$9),Data16!$O:$U,4,FALSE),"Not found- Please Review"))

[Hercules1946]

Hello
I have spent some time looking at your data, and based on what you have said it should do, I can't find anything wrong. Your saying that where the concat gives a duplicate lookup value, this creates a choice of completion dates, and in this event you want to return the earliest of the two dates?
Looking at the data16 tab I found 11 instances of duplicates with different completion dates, and in each case the earlier date is returned. Here are two examples:

ID 23128 Completions: 4/9/2015, 18/3/2016. Date returned: 4/9/2015
ID 23474 Completions: 4/11/2015, 14/3/2016 Date returned: 4/11/2015

This appears to be because the completion dates are in ascending order, which I suggested as a solution in my earlier post.

If there is still a problem, please can you repost the data, clearly marking which formulae are returning the wrong values, and telling us what the values should be. To keep it simple just post a small number of entries that show the issue.

[Hercules1946]

Sorry somehow my post was duplicated so I hope this will remove it.

[imadhasan]

Hercules, when changed the ascending order overall,i did not notice any changes but focusing on the instances you mentioned, it does seem to work.

I play around a bit and it does seem to be working!

Thanks a lot!

[Hercules1946]

Your Welcome.