Index & Match for 3 criteria

[Eric Tsang]

Dear all

I would like to know how i can get the Index & Match function works on 3 creteria

See the attached

=INDEX($B$7:$C$13,MATCH(G$6,Date,1),MATCH($E7,hotel,0))

I need to add one more criteria to match for the column. that is "Room"

I tried =INDEX($B$7:$C$13,MATCH(G$6,Date,1),MATCH($E7,hotel,0)*($E8,room,0)) , it does not work


Appreciated your help

[bebo021999]

There is only one table of rate for single hotel. Where is another one for second hotel?

[Eric Tsang]

Bebo

There is two hotel, Kowloon and Eaton

Attachment 664553

Thanks

[bebo021999]

Bebo

There is two hotel, Kowloon and Eaton

Attachment 664553

Thanks

Yes, we know that. But, the rate tarif (for both rooms) seems for 1 hotel only.

[Eric Tsang]

Bebo


The room rates are different

Kowloon & Eaton has their own rates

Hence, i have to locate the rates by the date different

Eric

[bebo021999]

I see only one rates of one hotel.
Annotation 2020-02-26 103432.jpg

[Eric Tsang]

Bebo

the 1st hotel is Kowloon Hotel, room type is Deluxe in 1-May, the rate is 950
the 2nd hotel is Eaton Hotel, room type is Superior in 1-May, the rate is 633

Eric

[Eric Tsang]

Bebo

So, i solve the problem of different date with different rate for different hotel

But i also want to add the criteria of room type.

So, user need to match, the date, hotel and room type for the rates.

Eric

[bebo021999]

So, Kowloon sticks to Deluxe, and Eaton to Superior?
Finally, there are 2 criterias only: Date and Room (or Hotel)

[Eric Tsang]

Dear Bebo

See below

Attachment 664566

Eric

[Eric Tsang]

Dear Bebo

I watched a suggestion from Youtube
https://www.youtube.com/watch?v=Z2X9j8sqJ64&t=354s

But this is an array formula, i do not know how to copy across to other cells

Eric

[bebo021999]

Dear Bebo
I watched a suggestion from Youtube
https://www.youtube.com/watch?v=Z2X9j8sqJ64&t=354s
Eric

It is quite different with multi columns.
In your file, as I said before, there are 2 columns of rate, with hotel1&room1 for column1, hotel2&room2 for column2, you need only one criteria for hotel (or room) is enough.
Anyway, below formula can do with separate hotel & room:
In G7:

=INDEX($B$7:$C$13,MATCH(G$6,Date,1),MATCH($E$7&"|"&$F$7,INDEX(hotel&"|"&Room,),0))

[Eric Tsang]

Bebo

Thanks and i think i still do not understand "|"mean

Eric

[bebo021999]

Bebo

Thanks and i think i still do not understand "|"mean

Eric

"|" or any special symbol (@,!,... to keep hotel & room together but in separate meaning.
Imagin,
case1: Leon & Ate
Case 2: Leo & Nate
concanate together to be the same string: "Leonate"

[Eric Tsang]

Bebo

Thanks
eric