Match on two criteria

[Ibrarbutt]

I want to match Hotel Name, then Room Type, if matching then bring the rate from Table 1 to table 2


Table 1

Hotel Name - Room Type - Rate


Royal Hotel - Single - 120
Priceless Hotel - Twin - 140
Royal Hotel - Twin (BB) - 145
.
.
.
.
.
.



Table 2

If I select

Hotel Name - Room Type - Rate

Royal Hotel - Single - (this rate will bring from table 1)

 

[arthurbr]

Is the data in three different columns ?

 

[Ibrarbutt]

yes
it is three different columns

Table 1


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Hotel Name
[/TD]
[TD]Room Type
[/TD]
[TD]Rate
[/TD]
[/TR]
[TR]
[TD]Royal Hotel <strike></strike>
[/TD]
[TD]Single
[/TD]
[TD]120
[/TD]
[/TR]
[TR]
[TD]Prince Hotel
[/TD]
[TD]Twin
[/TD]
[TD]135
[/TD]
[/TR]
[TR]
[TD]Royal Hotel <strike></strike>
[/TD]
[TD]Triple
[/TD]
[TD]145
[/TD]
[/TR]
[TR]
[TD]Ezdan
[/TD]
[TD]Single
[/TD]
[TD]160
[/TD]
[/TR]
[TR]
[TD]Royal Hotel <strike></strike>
[/TD]
[TD]Single (BB)
[/TD]
[TD]165
[/TD]
[/TR]
</tbody>[/TABLE]














Table 2

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Hotel Name
[/TD]
[TD]Room Type
[/TD]
[TD]Rate
[/TD]
[/TR]
[TR]
[TD]Royal Hotel <strike></strike>
[/TD]
[TD]Single
[/TD]
[TD]from table 1
[/TD]
[/TR]
[TR]
[TD]Royal Hotel <strike></strike><strike></strike>
[/TD]
[TD]Twin
[/TD]
[TD]from table 1<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Ezdan
[/TD]
[TD]Triple
[/TD]
[TD]from table 1<strike></strike>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[arthurbr]

Have a look at https://spreadsheeto.com/index-match/ (steps 3 4 5)

 

[xaritona89]

Hotel Name Room Type Rate
Royal Hotel Single 120
Prince Hotel Twin 135
Royal Hotel Triple 145
Ezdan Single 160
Royal Hotel Single (BB) 165

Hotel Name Room Type Rate
Royal Hotel Single 120
Royal Hotel Twin #N/A
Ezdan Triple #N/A


rate:
=INDEX(A$2:C$6,MATCH(1,(A$2:A$6=A9)*(B$2:B$6=B9),0),3)
ctrl + shift+enter
copy down

this information copy paste in excel
and view better

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Hotel Name[/TD]
[TD]Room Type[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Royal Hotel[/TD]
[TD]Single[/TD]
[TD]

120​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Prince Hotel[/TD]
[TD]Twin[/TD]
[TD]

135​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Royal Hotel[/TD]
[TD]Triple[/TD]
[TD]

145​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Ezdan[/TD]
[TD]Single[/TD]
[TD]

160​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Royal Hotel[/TD]
[TD]Single (BB)[/TD]
[TD]

165​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Hotel Name[/TD]
[TD]Room Type[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Royal Hotel[/TD]
[TD]Single[/TD]
[TD]

120​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]Royal Hotel[/TD]
[TD]Twin[/TD]
[TD]no match[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]Ezdan[/TD]
[TD]Triple[/TD]
[TD]no match[/TD]
[/TR]
</tbody>[/TABLE]


c15=IF(SUMIFS($C$2:$C$6,$A$2:$A$6,A15,$B$2:$B$6,B15)=0,"no match",SUMIFS($C$2:$C$6,$A$2:$A$6,A15,$B$2:$B$6,B15)) copy down

 

[Aladin Akyurek]

Select the hotel cells of Table 1 and name the selection hotel,
Select the cells of room type of Table 1 and name the selection roomtype.
Select the cells housing rates of Table 1 and name the selection rate.

Let A:C of a different sheet house Table 2.

In C2 of this sheet, control+shift+enter, not just enter, and copy down:

=IFNA(INDEX(rate,MATCH(B2,IF(hotel=A2,roomtype),0)),"not available")

If you get a #NAME ? error, replace IFNA with IFERROR and apply control+shift+enter.

 

[razaas]

Suppose your table 1 range B2:D7 and table 2 range B9:D14 with rows 2 and 9 are headers, enter this formula in D10 and drag it down: "=INDEX($D$3:$D$7,MATCH(B10:B10&C10:C10,$B$3:$B$7&$C$3:$C$7,0),)" ctrl + shift + enter.