Get MIN of range using VLOOKUP and INDIRECT

**jrtaylor** · 08-15-2021, 02:13 PM

I've attached a sample workbook which contains 3 sheets.

The first sheet is the "Analysis" sheet which looks up data in the other sheets. (The actual workbook I'm using has over 50 sheets).

In this sample workbook, I've named two ranges. The range names are the same as the sheets, concatenated with an "R". So the range I'm using in the AAVE sheet is named AAVER.

I am able to pull up data for single cells in the two ranges using VLOOKUP and INDIRECT. (See columns J:N of the Analysis sheet).

However I want to also obtain the MIN of a range of data in each of the two sheets, also using some kind of lookup formula. (or possibly LAMBDA?).

Any suggestions on how to accomplish this? I appreciate your help!

**Bo_Ry** · 08-15-2021, 03:01 PM

Maybe try

=MINIFS(INDIRECT(H3),INDIRECT(H3),">0")

**jrtaylor** · 08-15-2021, 03:33 PM

That was clever and efficient! Thanks much.

Edit: Whoops. Your formula gives min of the entire range. I'm looking for min of start date (Column F in Analysis sheet) + 60 days out.

**JohnTopley** · 08-15-2021, 03:44 PM

The MINIFS is looking the named range in total, not the subset you indicated in you workbook.

By coincidence the MIN happens to be in your selected range: FALSE positive!

NOTE: your 60 day selection is actually 61 days.

**jrtaylor** · 08-15-2021, 04:32 PM

Thanks John. I reverted the thread to "unsolved." And noted, on my 61 day range selection!

**Bo_Ry** · 08-16-2021, 04:50 AM

Try

=INDEX(SORT(FILTER(INDIRECT(H3),(INDEX(INDIRECT(H3),,1)>=F3)*(INDEX(INDIRECT(H3),,1)<=F3+Q$2)),3),1,3)

Don't use the whole column for the range name, this really slows down array calculation.
Row 5:10000 covers 27 years is more than enough.

or faster with minifs

=MINIFS(INDEX(INDIRECT(H3),,3),INDEX(INDIRECT(H3),,1),">="&F3,INDEX(INDIRECT(H3),,1),"<="&F3+Q$2)

**kvsrinivasamurthy** · 08-16-2021, 05:28 AM

Pl see file. In R3 copied down

Please Login or Register  to view this content.

**jrtaylor** · 08-16-2021, 11:07 AM

Thanks so much for the help! And I'll shorten the range down to 1,000 rows since that's more than enough to cover the time span of the price history.

Get MIN of range using VLOOKUP and INDIRECT

LinkBack

Thread Tools

Rate This Thread

Display

Get MIN of range using VLOOKUP and INDIRECT

Re: Get MIN of range using VLOOKUP and INDIRECT

Re: Get MIN of range using VLOOKUP and INDIRECT

Re: Get MIN of range using VLOOKUP and INDIRECT

Re: Get MIN of range using VLOOKUP and INDIRECT

Re: Get MIN of range using VLOOKUP and INDIRECT

Re: Get MIN of range using VLOOKUP and INDIRECT

Re: Get MIN of range using VLOOKUP and INDIRECT

Thread Information

Users Browsing this Thread

Similar Threads

Using a Text Value as Range by Indirect Function in a Vlookup to get a corresponding value

using indirect in vlookup to insert search range

[SOLVED] Keeping cell range locked during Vlookup? Indirect function?

[SOLVED] Vlookup/Indirect Formula Not Finding Range

Vlookup with Indirect and Named Range

INDIRECT.EXT to build a range in VLOOKUP

Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions