Hi All,
Help please... I need to develop an INDEX MATCH formula to ignore/skip blanks within row/range lookup.
I have attached an example workbook that highlights (yellow cell fill) the current INDEX MATCH formula that returns an "#N/A", and also an example of the expected results. Essentially the INDEX MATCH formulas get their ranges from an INDIRECT scenario but are pretty standard otherwise.
Any help will be greatly appreciated. Thank you
Hi. Before we get going on this one...
Is your forum profile showing the Excel PRODUCT that you need this request to work with? There is no such thing as Excel 210...
The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.
The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.
Cheers,
Glenn.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks for the version update! Rather than show us formulae that don't quite work... what IS the expected result in D8 to E9?
Hi,
Excel product = amended
Within the example workbook attached I have included in D28:E29 the expected result.
LoL... I didn't scroll down. Do you REALLY want a range as the answer??? $J$11:$J$13?? Will that make sense to anyone?
Change J&K10 to match E&F10.
Your approach is likely to be inefficient... all those INDIRECTS recalculating every time anything changes...
Having the range returned to then be used in the COUNTIFS formulas as INDIRECT ranges within F:AI was the only way I could think of doing it. Although the column headers in D7:E7 are misleading as they are helper columns and would be hidden columns in the master version. Although I suppose once the range return formula is figured out then it could instead be incorperted directly into the COUNTIFS.
OMG... I feel soo stupid. Such a simple error. Thank you soo much for spotting this mistake.
I agree that the use of INDIRECT will be very inefficient as I scale... do you have a suggestion for how I could go about returning the range in a different way?
If your data is laid out in a nice sensible way, it could be simpler. Task 1 begins on D9, Task2 on I9... If task 3 begins on N9, task 4 on S9, etc....
it can be made much more efficient. How are subsequent tasks listed (I fear that I know already... instead of all being on the same row - GOOD, they will split onto other rows further down - MESSY!!!)?
Correct... "Task 3" = N9, "Task 4" = S9
Subsequent tasks will always begin listing on row 11, although the quantity of assets/dates will vary per task
OK. GOOD....
So, set up 2 Named Ranges (called START and END - what else???):
=INDEX(Sheet2!$1:$1048576,11,MATCH($C8,Sheet2!$9:$9,0)+1):INDEX(Sheet2!$1:$1048576,39,MATCH($C8,Sheet2!$9:$9,0)+1)
=INDEX(Sheet2!$1:$1048576,11,MATCH($C8,Sheet2!$9:$9,0)+2):INDEX(Sheet2!$1:$1048576,39,MATCH($C8,Sheet2!$9:$9,0)+2)
CTRL-F3 to view/edit them.
Then use this in D8 copied across and down:
=SUMPRODUCT((Start<=D$3)*(End>=D$3))
You are a superstar !!
I would have never thought of taking that approach but understand the formula / named range combo.
Thank you soo much for your help
Rep added
Thread marked as solved
You're welcome and thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks