Log In
Register
Home
July 11, 2017
Offline
Hi There,
I need to find a value that is looking up 3 values., Value 1 is the Group, i.e. Hilton, Value 2 is the week i.e W1, value 3 is the Occupancy %
I have tried creating a VLookup,Index,Match formular but both the Week & Occupancy ref are in the same column
Week is in cell AC7
Occupancy is in cell AC8
I believe the index formular looks up row then column, but need to look up column then column again
=VLOOKUP($BA$2,'REV OCC'!E8:AKF322,(INDEX('REV OCC'!E8:AKF322,MATCH(BA4,'REV OCC'!E8:AKF8,0),MATCH(A3,'REV OCC'!N7:AKF7,0))),0)
Any help would be greatly appreciated
Paul
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Paul
If your data is consistent, you can use INDEX/MATCH.
To get the 2nd row (Occupancy%), just add 1 to the MATCH value.
Please refer to the attachment.
Hope this helps.
Sunny
July 11, 2017
Offline
Hi Sunny,
Thanks for the reply, however unable to make the formula work due to how the info is laid out, have attached a 'clean' version of the report so you have a better idea what I require.
FYI the reason I'm doing this is because I need to add columns into the 'Rev-Occ' data tab which means I would have to update every VLookup column index
Thanks
Paul
July 11, 2017
Offline
sorry forgot to attach file
VIP
Trusted Members
June 25, 2016
Offline
Hi Paul
Take a look at the attachment.
The final formula may look complicated but the logic is easy to understand.
Hope this helps.
Sunny
July 11, 2017
Offline
Thanks Sunny,
You are a star!!!
Regards
Paul
July 11, 2017
Offline
Hi Sunny,
Really sorry but have found an issue, when I updated the other columns to have W1, W2, W3 etc the formula does not work as it does not refer to the header in E4 'Occupancy' which is in row 8 on the 'Rev'Occ'?, I tried adding another MATCH formula but it did not work.
Any thoughts?
Thanks
Paul
VIP
Trusted Members
June 25, 2016
Offline
Hi Paul
The data that you gave us earlier is very different from the one you gave us now. The suggested solution is based on what you gave us earlier.
The header is a critical component for the lookup and should have been given to us in full and not amended later that ruined the solution.
If you need to have W1, W2 etc in multiple columns as a header, I suggest you insert them in row 8.
Leave row 7 as it is with only a single W1, W2 etc but hide the entire row 7.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Hi Paul
My 2nd attempt
I created a helper row 7 that concatenated rows 8 and 9 to create a unique value.
I then use the same technique as my previous suggestion to get the row and column.
You can hide row 7.
Hope this helps.
Sunny
Answers Post
1 Guest(s)
