Complex find, lookup & import range of data/cells

0
votes

I'm asking about the best way to filter, import, lookup, the data from a sheet looking like that. that's a finale datasheet to import multiple room types with all its prices, from different hotels, from different sheet. see the image below,

  • First column has a Hotel code(no duplication)

  • then it can have 1~12 rooms (name of room under the Room 1/2/3) can be duplicate

  • then you will see in the sheet the breakdown of prices for adult children and so on for each room.

Now I'm stuck for a couple of days for a method to filter and import only the criteria I'm looking for.

  • first it must bring the hotel range (because of multiple hotels on 1 sheet)

  • then select the room and import the prices belong to it,

  • then find the prices for the specific date entered. the last part is the easy 1 with just a vlooup. I'm stuck in the structure of begging.

Here's a copy of sheet, Editable and photo of the copied range

https://docs.google.com/spreadsheets/d/1UMKSJ43wByqnCzzZZY9qbzoL9rD0rH8A4_sF0djWpLI/edit?usp=sharing

Screenshot

The required search filters are in Sheet ('Tools')

1
google-sheetsmatchdropdowngoogle-sheets-formulaarray-formulas
@ScottCraner Google Spreadsheet - Coda Raafat
Anybody can help ?? i'm going seriously crazy here - Coda Raafat
i cannot use index in lookups because columns changes, i cannot use hlookups coz not all the columns all rows have the value i'm looking for, i cannot figure out the import range based on specific cell and import the range that belong to that cell, i cannot figure out a way to import the dynamic range of cells and rows - Coda Raafat

1 Answers

0
votes

you can solve this by using side columns to calculate items for dropdown for example like this:

0