Vlookup not selecting first cell from top.

[DeonvZ]

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. 

[#2]

@DeonvZ 

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.

[#3]

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?

[#4]

@Jan Karel Pieterse 

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 

[#5]

Thanks Hans, I'll take a look. I've not used XLookup before.
Regards
Deon

[#6]

@DeonvZ 

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.

[#7]

@Hans Vogelaar 

File now attached.

[#8]

I think this is simply how VLOOKUP works and has always worked. With Excel 2010 I get identical results on your sample file. If you want to control the search direction, consider the XLOOKUP function, which has a separate argument to control this.

[#9 · Accepted solution]

@DeonvZ 

See the attached version. It has VLOOKUP with FALSE as 4th argument and XLOOKUP.

[#10]

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