Using INDEX/MATCH or other to find first record meeting criteria

[glamlife]

Hello!

I've been struggling trying to come up with a formula for this situation.

I have an Excel model with two basic levels of data: Plots of land and Hotel buildings on them. (I am using Excel 2007.)

Book2
	A	B	C	D
1	BldgID	PlotID	No.ofRooms	MainBldg?(T/F)
2	B-01	P-01	69
3	B-02	P-01	102
4	B-03	P-01	97
5	B-04	P-02	40
6	B-05	P-02	120
7	B-06	P-02	56
8	B-07	P-03	170
9	B-08	P-03	88
10	B-09	P-03	140
11	B-10	P-03	220
Sheet1

I am trying to figure out a formula which will identify the first Hotel building on the plot to have 100 rooms or more. This will be identified as the "Main Building", and this status will be used to trigger other formulas in the spreadsheet (not shown).

My guess is that some combination of Index/Match with a logic statement would work, but I cannot figure out how to construct it.

Any help will be much appreciated!

Matt (glamlife)

 

[DonkeyOte]

Formula for D2

Excel Workbook
	D
2	B-02
Sheet3

http://www.excel-jeanie-html.de/index.php?f=1Would return first value in A where value in C >= 100 for given plot B -- will only show 1 building per plot.

HTH

 

[glamlife]

Thanks so much for your help!

I should've known it would take a CSE formula... those are my kryptonite.

Matt (glamlife)

 

[DonkeyOte]

Not a problem, there's plenty more from where that came from.

Lex

 

[glamlife]

Hi again.

Sorry I've just realised this isn't quite the solution I needed - but I think it's a variation on the same formula.

I'd like the formula to produce a TRUE/FALSE result - the condition being that if the building in that row is the first building on the plot to have 100 or more rooms, the result will be TRUE, otherwise FALSE.

Hope that makes sense!

Thanks,

Matt

 

[DonkeyOte]

Excel Workbook
	D
2	FALSE
Sheet3

 

[glamlife]

Thanks Lex. I've tried it now and it works fine. Much appreciated.

Matt