|
|
|
|
|
|
|
|
|
|
|
| |
 Answer |
|
| |
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" 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 |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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)):INDEX(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 |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <RachelM@discussions.microsoft.com> wrote in message
news:5DE1F681-7D20-41F1-B380-FAEAC2B7E312@microsoft.com...
> 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)):INDEX(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
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
| |
Answer |
|
| |
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
"Tkydon" <Tkydon@discussions.microsoft.com> wrote in message
news:06E920A1-F94D-420E-84E4-20AD20624044@microsoft.com...
> 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?
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Notify me of replies