Index with if nested formula

[MEHTA JR]

I Want to add place reference in the formula

right now formula is working correct but if two place have same rate than it takes both place rates.

Place	Hotel Name	Rate
Shimla	Sukh Sagar	3000
Dharamshala	The Eden	3000

<tbody>
</tbody>

Shimla Hotel Name Dharamshal

3000 Sukhsagar (This Display) 3000 Sukhsagar (Not display)
The Eden (Not Display) The Eden (This Display)

Given Formula

{=INDEX($B$7:$B$16,SMALL(IF($E$19=$C$7:$C$16,ROW($C$7:$C$16)-ROW($C$7)+1),ROW(2:2)))}

In Shimla only Sukhsagar Displayed not The Eden and in case of Dharamshala only The Eden Displayed not Sukhsagar

How I can give Hotel name reference in formula

please advise

Jiten

 

[Legacy 143009]

How will the data look like in the end? What do you want to have exactly?

 

[MEHTA JR]

City Hotel Name Rent Dalhousie The Ark 2600 Dalhousie Mohan Palace 2600 Amritsar Ritz Plaza 3500 Amritsar Namskar Residenc 2600 Manali Khooshbu Resort 2200 Manali Utopia 2000 Dharamshala Eden 3000 Shimla Sukhsagar 3000 Shimla De Park 2500 <tbody> </tbody><colgroup><col><col><col></colgroup>



<tbody>
</tbody><colgroup><col><col><col></colgroup>

	Shimla	Hotel	Dharamshala	Hotel
Rent	3000	Eden	3000	Eden
		Sukhsagar		Sukhsagar

<tbody>
</tbody><colgroup><col><col span="4"></colgroup>

When I give following formula to give the list of hotel according to rent cell value i.e when I entered 3000 then it give me 2 hotel name but hotel sukhsagar is belonging to Shimla not Dharamshal while hotel eden is belonging to Dharamshal not shimal.

but when I put 3000 against Shimla it gives me only Shimla belonging hotel details.

please help me to sort out this problem


Jiten

 

[Aladin Akyurek]

Row\Col	A​	B​	C​
1​	City	Hotel Name	Rent
2​	Dalhousie	The Ark	2600​
3​	Dalhousie	Mohan Palace	2600​
4​	Amritsar	Ritz Plaza	3500​
5​	Amritsar	Namskar Residenc	2600​
6​	Manali	Khooshbu Resort	2200​
7​	Manali	Utopia	2000​
8​	Dharamshala	Eden	3000​
9​	Shimla	Sukhsagar	3000​
10​	Shimla	De Park	2500​
11​	Rent	Shimla	Dharamshala
12​	3000​	Sukhsagar	Eden
13​
14​

In B12 control+shift+enter, not just enter, copy across, and down:

=IF(ROWS(B$12:B12)>COUNTIFS($A$2:$A$10,B$11,$C$2:$C$10,$A12),"",INDEX($B$2:$B$10,SMALL(IF($C$2:$C$10=$A12,IF($A$2:$A$10=B$11,ROW($B$2:$B$10)-ROW($B$2)+1)),ROWS(B$12:B12))))

 

[MEHTA JR]

Thanks Bro

Really it works

 

[Aladin Akyurek]

Thanks Bro

Really it works

You are welcome.

 

[MEHTA JR]

Dear Friend

Would you recommend me way to get good grip in excel & VBA.


Thanks

Jiten

 

[Aladin Akyurek]

Dear Friend

Would you recommend me way to get good grip in excel & VBA.


Thanks

Jiten

See if this helps: https://www.mrexcel.com/forum/excel...xamples-learn-vba-short-time.html#post4957623