cell value as a row number in a function?
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Rachel_M" schrieb im Newsbeitrag
...
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

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)):INDE X(B:B,VLOOKUP(F1,Sheet2!A2:B27,2,0)))



Regards,

Peo Sjoblom




"Rachel_M" wrote:

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

Thanks! That worked!

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Rachel_M" schrieb im Newsbeitrag
...
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

Thanks to you as well, because that worked too!

"Peo Sjoblom" wrote:

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)):INDE X(B:B,VLOOKUP(F1,Sheet2!A2:B27,2,0)))



Regards,

Peo Sjoblom




"Rachel_M" wrote:

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

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)



"Rachel_M" wrote in message
...
Thanks to you as well, because that worked too!

"Peo Sjoblom" wrote:

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)):INDE X(B:B,VLOOKUP(F1,Sheet2!A2:B27,2,0)))



Regards,

Peo Sjoblom




"Rachel_M" wrote:

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

cell value as a row number in a function?
 

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?

cell value as a row number in a function?
 

Hi,

Try this

=HLOOKUP(A68,(INDEX("'"&indirect(A2&"_table'!A:A") ,(C68*3+2)):INDEX("'"&indirect(A2&"_table'!Q:Q"),4 1)),((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

"Tkydon" wrote in message
...
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?