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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Place
[/TD]
[TD]Hotel Name
[/TD]
[TD]Rate
[/TD]
[/TR]
[TR]
[TD]Shimla
[/TD]
[TD]Sukh Sagar
[/TD]
[TD]3000
[/TD]
[/TR]
[TR]
[TD]Dharamshala
[/TD]
[TD]The Eden
[/TD]
[TD]3000
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 292"]
<tbody>[TR]
[TD][TABLE="width: 292"]
<tbody>[TR]
[TD]City[/TD]
[TD]Hotel Name[/TD]
[TD]Rent[/TD]
[/TR]
[TR]
[TD]Dalhousie[/TD]
[TD]The Ark[/TD]
[TD="align: right"]2600[/TD]
[/TR]
[TR]
[TD]Dalhousie[/TD]
[TD]Mohan Palace[/TD]
[TD="align: right"]2600[/TD]
[/TR]
[TR]
[TD]Amritsar[/TD]
[TD]Ritz Plaza[/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD]Amritsar[/TD]
[TD]Namskar Residenc[/TD]
[TD="align: right"]2600[/TD]
[/TR]
[TR]
[TD]Manali[/TD]
[TD]Khooshbu Resort[/TD]
[TD="align: right"]2200[/TD]
[/TR]
[TR]
[TD]Manali[/TD]
[TD]Utopia[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]Dharamshala[/TD]
[TD]Eden[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]Shimla[/TD]
[TD]Sukhsagar[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]Shimla[/TD]
[TD]De Park[/TD]
[TD="align: right"]2500[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]
[TABLE="width: 518"]
<tbody>[TR]
[TD] [/TD]
[TD]Shimla[/TD]
[TD]Hotel[/TD]
[TD]Dharamshala[/TD]
[TD]Hotel[/TD]
[/TR]
[TR]
[TD]Rent[/TD]
[TD="align: right"]3000[/TD]
[TD]Eden[/TD]
[TD="align: right"]3000[/TD]
[TD]Eden[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD]Sukhsagar[/TD]
[TD="align: right"][/TD]
[TD]Sukhsagar[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]


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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]City[/td][td]Hotel Name[/td][td]Rent[/td][/tr]
[tr][td]

2​

[/td][td]Dalhousie[/td][td]The Ark[/td][td]

2600​

[/td][/tr]
[tr][td]

3​

[/td][td]Dalhousie[/td][td]Mohan Palace[/td][td]

2600​

[/td][/tr]
[tr][td]

4​

[/td][td]Amritsar[/td][td]Ritz Plaza[/td][td]

3500​

[/td][/tr]
[tr][td]

5​

[/td][td]Amritsar[/td][td]Namskar Residenc[/td][td]

2600​

[/td][/tr]
[tr][td]

6​

[/td][td]Manali[/td][td]Khooshbu Resort[/td][td]

2200​

[/td][/tr]
[tr][td]

7​

[/td][td]Manali[/td][td]Utopia[/td][td]

2000​

[/td][/tr]
[tr][td]

8​

[/td][td]Dharamshala[/td][td]Eden[/td][td]

3000​

[/td][/tr]
[tr][td]

9​

[/td][td]Shimla[/td][td]Sukhsagar[/td][td]

3000​

[/td][/tr]
[tr][td]

10​

[/td][td]Shimla[/td][td]De Park[/td][td]

2500​

[/td][/tr]
[tr][td]

11​

[/td][td]Rent[/td][td]Shimla[/td][td]Dharamshala[/td][/tr]
[tr][td]

12​

[/td][td]

3000​

[/td][td]Sukhsagar[/td][td]Eden[/td][/tr]
[tr][td]

13​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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