• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup problem.

riya

Member
HI All,
I have a attached problem where vlookup is not working properly, please let me how to resolve it.

Attached is the test file for your reference.
Thank You,
 

Attachments

vletm

Excel Ninja
riya
VlookUp works correct
... but Your way to use it, it gives different result than You seems to expect.
Screenshot 2019-11-19 at 14.24.16.png
to get those 2 & 3 ... You should able to get row #33 values ... not row #32.
Both of those are 'CanadaERS'.
 

Peter Bartholomew

Well-Known Member
I got a nasty shock when I downloaded the file; it fired up in Excel 2010 rather than 365! I don't know what to do anymore with no dynamic arrays. Anyway once saved and reloaded and all was well.

My final formula looks innocent enough
= XLOOKUP( 1, filteredCountryCode, filteredDataset )

The catch is setting up the lookup array and the return array. To do this, I started with a search for the correct number dataset
= XLOOKUP( @Set, datasetNum, data )
That returns an entire column but includes the blank subranges so, to eliminate them, I filtered the dataset
= FILTER( dataset, ISNUMBER(dataset) )

To make matching changes to the lookup array, I also filtered that
= FILTER( (CountryList=@Country) * (CodeList=@Code), ISNUMBER(dataset) )
This process eliminates the duplicated search criteria for Canada and the first XLOOKUP above returns the correct values.

Sadly the result is not a spilt array but one can't have everything!
 

Attachments

vletm

Excel Ninja
riya
Your ... didn't understand ...
Reread syntax about Vlookup
- You're getting always same rows results, but You need results from two rows.
 
Top