INDEX/MATCH with a variable MATCH lookup_array?

**mrbusto71** · 05-21-2017, 01:38 PM

Howdy folks! First, here's my current formula:

Please Login or Register  to view this content.

This formula works currently, but is there a way to have the lookup_array portion of the MATCH do a validation check?
i.e. MATCH(lookup value,if this criteria is met then lookup is column B, rows 2-35, if this criteria is met, column C, rows 2-35, etc,0)?

I couldn't find anything online and my attempts at having an INDEX/MATCH formula with multiple Match criteria over diff tabs/diff range sizes was a no go.

Any advice would be appreciated!

**Glenn Kennedy** · 05-21-2017, 02:04 PM

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**Glenn Kennedy** · 05-21-2017, 02:06 PM

In particular, could you malke it clear if you are looking for ONE matching result... or several...

**mrbusto71** · 05-21-2017, 03:05 PM

Sure thing! Workbook attached.

Based on the attached workbook, all I'm trying to do is have a formula in B1 on the Staff List tab do the following:
Check the GOAL tab (cell B2, specifically) to see what date to work with. Once that date is identified, search for it on the Calendar tab to find which column we'll be using to pull our data. Once the column is identified, see if the name in column A on the Staff List tab is present. If not, return a blank, if so, return the Assignment from column A on the Calendar tab.

Basically I just want the Staff List tab to show who is assigned to what project based on the date on the GOAL tab, if that makes sense.
Thanks for the help!

**bebo021999** · 05-21-2017, 09:25 PM

Try in B1 then drag down:

Please Login or Register  to view this content.

**Glenn Kennedy** · 05-22-2017, 04:54 AM

or, if you prefer avoiding the use of the volatile OfFSET function, use this formula:

=IFERROR(INDEX(Calendar!$A$2:$A$11,SUM(INDEX((Calendar!$B$2:$H$11=A1)*(Calendar!$B$1:$H$1=GOAL!$B$2)*ROW(Calendar!$B$2:$H$11),))-1),"")

**mrbusto71** · 05-22-2017, 03:08 PM

Thanks bebo and Glenn! Both worked beautifully, though I've chosen to use Glenn's only because I understand it a smidge better.

That said, Glenn, can this be a teachable moment? I'm curious how you knew to do this without using the match function. It seems like I could brush up on how to utilize the nested Index/Sum function, but understanding how you made this work will help me know how to do it in the future.

If you can explain I'd be grateful, but if not, still super thankful for the working formula regardless!

**Glenn Kennedy** · 05-22-2017, 03:27 PM

Calendar!$B$2:$H$11=A1 assembles a list (array) of TRUE or FALSE for every cell in the range, depending on whether or not it is = A2.

Calendar!$B$1:$H$1=GOAL!$B$2 returns an array of TRUE/FALSE for the dates in row 1, depending on whether or not they're equal to Goal B2. The first array is multiplied by the second and then multiplied by the row number using this bit: ROW(Calendar!$B$2:$H$11)

This returns an array of numbers: 0 where 1+ of the conditions have not been met, or a number >0 for the matching occasion where all conditions have been met. SUm adds them up and 1 is subtracted to take account of the fact that the data starts in row 2.

What's left is INDEX (cells in row A, a number), form that Excel returns he matching Assignment number.

To follow what's going on, use formulas/evaluate formula.evaluate/evaluate/evaluate/etc when e.g. the first cell is selected. sadly the window can't be resized, but you can (with a bit of effort, or a bit of re-scaling of the ranges) really see what it's doing.

INDEX/MATCH with a variable MATCH lookup_array?

LinkBack

Thread Tools

Rate This Thread

Display

INDEX/MATCH with a variable MATCH lookup_array?

Re: INDEX/MATCH with a variable MATCH lookup_array?

Re: INDEX/MATCH with a variable MATCH lookup_array?

Re: INDEX/MATCH with a variable MATCH lookup_array?

Re: INDEX/MATCH with a variable MATCH lookup_array?

Re: INDEX/MATCH with a variable MATCH lookup_array?

Re: INDEX/MATCH with a variable MATCH lookup_array?

Re: INDEX/MATCH with a variable MATCH lookup_array?

Thread Information

Users Browsing this Thread

Similar Threads

IF Match formula - multiple sheets in lookup_array

use an 'index(match())' within the lookup_array of another match()?

[SOLVED] Lookup_array problem, when using MATCH function

Lookup_array NAME in a MATCH function

dynamic lookup_array in a match function

[SOLVED] INDEX, MATCH to "dynamic" lookup_array

Match function, changing range for lookup_array

Bookmarks

Bookmarks

Posting Permissions