Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
#VALUE in cell but pop up function box show right number | Excel Discussion (Misc queries) | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
multiply by actual number in cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |