cell value as a row number in a function?

[Rachel_M]

Hi, I'm trying to use returned values from VLOOKUP functions as the row
numbers in the range of a max function.

For example the first VLOOKUP gives me 9 and the 2nd VLOOKUP gives me 15,
and I want the maximum of cells B9:B15.
Thanks for any help!
Rachel

 

[Frank Kabel]

Hi
one way:
=MAX(INDIRECT("B" & VLOOKUP(...#1) & ":B" & VLOOKUP(...#2)))

 

[Peo Sjoblom]

One way

=MAX(INDEX(B:B,9):INDEX(B:B,15))

replace 9 and 15 with your respective vlookup

can look like this

=MAX(INDEX(B:B,VLOOKUP(E1,Sheet2!A2:B27,2,0)):INDEX(B:B,VLOOKUP(F1,Sheet2!A2:B27,2,0)))



Regards,

Peo Sjoblom

 

[Rachel_M]

Thanks! That worked!

Hi
one way:
=MAX(INDIRECT("B" & VLOOKUP(...#1) & ":B" & VLOOKUP(...#2)))

 

[Rachel_M]

Thanks to you as well, because that worked too!

One way

=MAX(INDEX(B:B,9):INDEX(B:B,15))

replace 9 and 15 with your respective vlookup

can look like this

=MAX(INDEX(B:B,VLOOKUP(E1,Sheet2!A2:B27,2,0)):INDEX(B:B,VLOOKUP(F1,Sheet2!A2:B27,2,0)))



Regards,

Peo Sjoblom

 

[Peo Sjoblom]

There is a benefit using INDEX contra INDIRECT, it's not volatile so you
want get prompted to save
if you just open and close the workbook and it won't recalculate every time
the sheet recalculates

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Tkydon]

This was really helpful.

Is there any way I can use the contents of the cell as part of a worksheet
name,such that a hlookup of another sheet could select a different sheet
based on the content of a cell, instead of having to use a long If function?

Cell Contents: No.1, No.2, No.3 or No.4

=HLOOKUP(A68,(INDEX('<cell contents>_table'!A:A,(C68*3+2)):INDEX('<cell
contents>_table'!Q:Q,41)),((13-C68)*3),FALSE))

Where <cell contents> is replaced by with the cell contents?

 

[Ashish Mathur]

Hi,

Try this

=HLOOKUP(A68,(INDEX("'"&indirect(A2&"_table'!A:A"),(C68*3+2)):INDEX("'"&indirect(A2&"_table'!Q:Q"),41)),((13-C68)*3),FALSE))

A2 holds the sheet name. I have not tested it, but it should work

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com