Index Match Question

[CharterJP]

Hi All,

I have an Index Match question. I would like to add additional lookups within the Index array. I thought the best way to do this was the following with the following formula:

=Index(array,Match(And(logic 1, logic 2), And(Array logic 1, array logic 2),0),# of columns)

However this isn't returning results just the #VALUE!

Any thoughts?

Thanks,

JP

[Richard Buttrey]

Hi,

You'll need to upload the workbook. Manually add the results you expect and explain how you've arrived at them.

[jhren]

...the following formula:

=Index(array,Match(And(logic 1, logic 2), And(Array logic 1, array logic 2),0),# of columns)

However this isn't returning results just the #VALUE!

...

Select the cell with formula. When it eveluates to an error, there should be "an exclamation mark in diamond" drop down beside the cell. Click on it and choose Show Calculation Steps... This usually shows the step just before final evaluation to an error result. Its meaningless if if its a multistep formula, so click on Evaluate then Restart. This will step into the calculation of the formula.... you'll figure the rest out.

Anyway, your AND functions within Match evaluate to TRUE or FALSE. While you could want to match TRUE or FALSE (though I'm doubting this is your current purpose), these will not point to an 1D array as the second argument... likely where the error starts, then cascades through the formula evalution.

[CharterJP]

Here is an example of what I am trying to do and the expected results.

[Ace_XL]

In b4 use this array formula

=INDEX(Data!$A$2:$D$30,MATCH($B$1&$A4,Data!$A$2:$A$30&Data!$B$2:$B$30,0), MATCH(B$3,Data!$A$1:$D$1,0))

Confirm with Ctrl+Shift+Enter and not just Enter

Copy down and across

[CharterJP]

Thank you Ace_XL for your reply. However, I could not get the formula to return the expected results. Could you provide more detail??

[Tony Valko]

Try this...

In A2 enter this label: # of Records

Enter this formula in B2:

=COUNTIF(Data!A:A,B1)

Enter this array formula** in B4:

=IF(ROWS(B$4:B4)>$B$2,"",INDEX(Data!C$2:C$30,MATCH(1,IF(Data!$A$2:$A$30=$B$1,IF(Data!$B$2:$B$30=$A4,1)),0)))

Copy across to C4 then down to B14:C14.

[Ace_XL]

Enclose the formula in IFERROR to avoid #N/A

See attached..

[CharterJP]

Hi Tony,

Interesting approach. I didn't think of adding a number of records to the form. However, I am getting a #N/A as a result for B and C. As I change the Value in B1 I can see the formula working however, the #N/A returns for the results exected for each B and C based on the number of roles each have

[CharterJP]

Ace,

Quick Question, You have the {} brackets before and after your formulas. However, when I add these to mine, it Nulls the formula. Why?

[Tony Valko]

Here's your file with the formulas implemented:

Multi Index Match Lookup Example(1).xlsx

Make sure you enter the formula in B4 as an array formula.

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.

[Ace_XL]

Ace,

Quick Question, You have the {} brackets before and after your formulas. However, when I add these to mine, it Nulls the formula. Why?

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[CharterJP]

Tony, thank you for the detailed explanation. I had no idea. I will give it a shot. You guys have been very helpful! I've learned something new today

[CharterJP]

Success! Ace and Tony... Thank you so much again!

[Tony Valko]

If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Not necessarily.

http://www.excelforum.com/suggestion...naccurate.html

[Tony Valko]

See this for more info on array formulas:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

[Tony Valko]

You're welcome. Thanks for the feedback!

[CharterJP]

Can you add this functionality within a LIST data validation?

[Tony Valko]

Yes, you would use one of the data ranges as the source for the drop down list.

You would need to use a dynamic range since not all the Names have the same amount of data.

Where do you want the drop down list to appear and what data should be used as the selections for the drop down list?