• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match Array workaround

W

westend9876

Member
Here is an array formula I have
{=IF(T69="","",INDEX(F77:F96,MATCH(TRUE,F77:F96<=W69,0)))}

However, I need to make an Indirect reference to F77 and F96.
Cell R69 contains the text "F77". Cell S69 contains text "F96".

Problem is that it seems array does not accept Indirect function. Is there a workaround for what I'm trying to do because each array formula will have a different range?
 
You can use with IFERROR, if you are using Excel 2007 or later

=IFERROR(YourFormula,"")

If output is an error, will give a blank cell. If you need something else to display, replace "" with your text
 
One more thing. Is there anyway to produce the same output without using an array? It takes a very long time to calculate all of my worksheets. Thanks.
 
Here is the revised formula based on feedback from Haseeb A:
{=IF(T69="","",INDEX(INDIRECT(R69&":"&S69),MATCH(TRUE,INDIRECT(R69&":"&S69)<=W69,0)))}

The range F77:F96 has numbers. Cell W69 contain a number.
Cell R69 contains the text "F77". Cell S69 contains text "F96".
 
INDIRECT is a volatile function, which will going to slow down your spreadsheet anyway. So can you share the idea why you are using an indirect ref.? Can it be replaced with named range? Cell R69 & Cell S69 do they receive these values through formula or manually entered?

Regards,
 
Somendra Misra said:
INDIRECT is a volatile function, which will going to slow down your spreadsheet anyway. So can you share the idea why you are using an indirect ref.? Can it be replaced with named range? Cell R69 & Cell S69 do they receive these values through formula or manually entered?

Regards,
Click to expand...
The range will change with every row. So I'm using helper columns R and S to hold the start and end ranges that each array formula should use.
 
Can you share a sample file with with the formula in 2 -3 row showing the idea of your working & which version of XL do you use?

Regards,
 
See this file. Formula is in green cell A1, rest related ranges are filled with yellow colour, also go through the named manager.

Check the performance of this method on your file.

Regards,
 

Attachments

  • westend_chandoo.xlsx
    9.5 KB · Views: 5
Hi. I'm not sure this example will work because the range will be different every row. It looks like you fixed the range. But, T69, T70, etc will all have varying ranges.
 
I will again request you to upload a sample file. with some two three example how range is changing how values are changing?

Regards,
 
You must log in or register to reply here.
Back
Top