Log In
Register
Home
Power Query
Word
May 3, 2019
Offline
Hello,
Looking for some help with a vlookup formula with multiple criteria. I have attached an excel file as an example. I am looking to bring in market data by region against the job title for which region that job title is in.
Please see attached example file.
Thank you.
Trusted Members
December 20, 2019
Offline
Very small data set to show how it works and the Greater Montreal heading had the word 'Area' missing which caused a problem!
In my opinion Index/Match/Match is ideal for a 2 way look up
=INDEX('Market Data'!$B$2:$D$4,MATCH('All Employes'!$A2,'Market Data'!$A$2:$A$4,0),MATCH($B2,'Market Data'!$B$1:$D$1,0))
Purfleet
Power Query
Word
May 3, 2019
Offline
Thank you for your reply. I understand the formula you provided and it works in this small data set. In my data set, it is returning #Ref. It may be that my reference arrays are not the same size...I will persevere...
Power Query
Word
May 3, 2019
Offline
I believe I found why it's returning #ref! On the 'All Employees' worksheet I have more rows that what is on the Market Data worksheet. I am trying to bring in the market against each employee's job title and the region they work in (i.e. could have multiple plumbers in various work regions) whereas the Market Data worksheet only has 1 row for plumber with the regional data listed across.
Any suggestions for a work around this new conundrum!
Thank you!
October 5, 2010
Offline
Hi AM,
Not sure why you are getting #REF without seeing what is causing that error.
If you have XLOOKUP you can use 2 nested XLOOKUP's - see attached.
Regards
Phil
1 Guest(s)
