cell value as a row number in a function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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



Regards,

Peo Sjoblom
 
Thanks to you as well, because that worked too!

Peo Sjoblom said:
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
Click to expand...
 
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)
 
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?
 
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?
 
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
 
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
 
Back
Top