• 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

  • test.xlsx
    13.2 KB · Views: 13
hello
you can to use this
Code:
=SUMPRODUCT(Input!$D$2:$F$1000,((Input!$B$2:$B$1000=$A2)*(Input!$C$2:$C$1000=C$1))*(Input!$D$1:$F$1=$B2))
 

Attachments

  • test1.xlsx
    13.3 KB · Views: 3
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'.
 
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

  • filtered lookup.xlsx
    19.6 KB · Views: 5
riya
Your ... didn't understand ...
Reread syntax about Vlookup
- You're getting always same rows results, but You need results from two rows.
 
Back
Top