using greater than in hlookup

[bsb2013]

I have a row C that has a series of increasing revenues. I want to find the first instance where the revenue is greater than a set cost stored in A1 and return the quarter name stored in row B. Is there a way to do this using hlookup? or is there another quick and simple way?

 

[njimack]

Having trouble understanding your layout, since you seem to be confused between rows and columns.

Assuming your data is in columns B and C, try the following array formula (confirmed with ctrl+shift+enter):
=INDEX(B1:B100,MATCH(MIN(IF(C1:C100>A1,C1:C100)),C1:C100,0))

 

[bsb2013]

sorry, i meant row 2 when I said b and row 3 when I said C

 

[njimack]

In that case, something like this:
=INDEX(A2:Z2,MATCH(MIN(IF(A3:Z3>A1,A3:Z3)),A3:Z3,0))
(ctrl+shift+enter)

 

[bsb2013]

I'm now using the match function to find the specific cell(thanks for bringing that to my attention!). Is there a function that calls a specific cell by taking in the row and column numbers of the cell? sort of like in vba when you say cells(row#,column#)

 

[njimack]

Sounds like you need to use INDEX in conjunction with MATCH. The 2nd and 3rd arguments of INDEX are the row and column.

 

[Peter_SSs]

Back to your original question. Doesn't this standard non-array formula do what you want?

Excel Workbook
	A	B	C	D	E	F
1	4.5	Q4
2	Q1	Q2	Q3	Q4	Q5	Q6
3	1	3	4	5	12	15
Look up Quarter

 

[pgc01]

Hi

Try also in B1:

=LOOKUP(A1,A3:F3,B2:G2)

 

[Peter_SSs]

PGC

In trying to make your formula fail biggrin I discovered that both yours and mine fail if the revenue being searched for is less that the Q1 revenue. In that case we could revert back to Neil's array formula, or make adjustments like this to mine (& yours - unless you have abetter adjustment).

=IF(A1< A3,A2,INDEX(A2:F2,MATCH(A1,A3:F3,1)+1))

 

[pgc01]

PGC

In trying to make your formula fail biggrin I discovered that both yours and mine fail if the revenue being searched for is less that the Q1 revenue.

I agree, my formula was just as good (bad) as yours.

You are right, the formula must be adjusted, or then use an array formula like Neil's.