Forum Discussion
Solved
Vlookup not selecting first cell from top.
Hi,
I have a table with more than one match for the Lookup_value. If there are four matches, then the result returned is from the last match found and not the first. I've solved this be sorting the Table_array from smallest to largest and the results column in the opposite order.
But is this normal that the lookup returns the result from the last match (top down) and not the first?
Thanks in advance for your assistance.
See the attached version. It has VLOOKUP with FALSE as 4th argument and XLOOKUP.
- Hi
All the discussion is correct, but these days I would use xlookup as it is much more flexible. However, there will always be a problem where the value being looked up occurs many times and has different values related to that lookup that need to be returned.
I have had this in the past (years ago) and there are two resolution. Firstly you have to determine a way that makes the lookup value a unique reference. Or secondly do you really want a lookup, is it in fact an average or a count or some other information that is needed?
I hope this helps
Regards
Paul - Depends. Are you absolutely positive those 4 matches are identical? Perhaps you can attach an Excel file which contains only those four entries and the vlookup formula you are using?
Thanks for responding. The matches are identical.
I've done a spreadsheet demonstrating the issue. I must be stupid, but I cannot find a place where I can attached the file. Perhaps you ca send me your email address.
Best Regards
Deon
If you cannot attach a workbook, you can make it available through OneDrive, Google Drive, Dropbox or similar - obtain a link to the uploaded file and paste the link into a reply.
If you are looking for an exact match, you should specify FALSE as 4th argument of VLOOKUP:
=VLOOKUP(lookup_value, lookup_range, column_index, FALSE)
This will return the value corresponding to the first match. If you have Microsoft 365 or Office 2021, you can use XLOOKUP - it provides more control over how Excel will search.
- Thanks Hans, I'll take a look. I've not used XLookup before.
Regards
Deon
