Jan 12 2023 06:34 AM
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.
Jan 12 2023 08:03 AM
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.
Jan 12 2023 08:06 AM
Jan 12 2023 11:20 PM
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
Jan 12 2023 11:22 PM
Jan 13 2023 04:49 AM
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.
Jan 15 2023 09:32 PM
Jan 16 2023 01:31 AM
Jan 16 2023 02:06 AM
SolutionSee the attached version. It has VLOOKUP with FALSE as 4th argument and XLOOKUP.
Jan 16 2023 03:55 AM
Jan 16 2023 02:06 AM
SolutionSee the attached version. It has VLOOKUP with FALSE as 4th argument and XLOOKUP.