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

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

<tbody>
</tbody>

Table 2

Hotel Name	Room Type	Rate
Royal Hotel <strike></strike>	Single	from table 1
Royal Hotel <strike></strike><strike></strike>	Twin	from table 1<strike></strike>
Ezdan	Triple	from table 1<strike></strike>

<tbody>
</tbody>

 

[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]

	A​	B​	C​
1​	Hotel Name	Room Type	Rate
2​	Royal Hotel	Single	120​
3​	Prince Hotel	Twin	135​
4​	Royal Hotel	Triple	145​
5​	Ezdan	Single	160​
6​	Royal Hotel	Single (BB)	165​
7​
8​
9​
10​
11​
12​
13​
14​	Hotel Name	Room Type	Rate
15​	Royal Hotel	Single	120​
16​	Royal Hotel	Twin	no match
17​	Ezdan	Triple	no match

<tbody>
</tbody>

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.