index match issue

**deanusa** · 03-31-2014, 12:57 AM

I had an issue in an earlier thread but may have explained it poorly and i know forum rules mean I need to start a new thread as really its a different issue.

In the attached spreadsheet I am trying to retrieve current Q %'s in column N based on a colour and an item, and a date.

R15, R16 and T6 criteria would change.

In column N I am able to retrieve the correct percentage, but only when the answer is in the first column.

In column O the same formula retrieves nothing.

I tried with an Index, Match, If array formula but couldnt get that to work either.

It's been driving me crazy all day, if anyone can work this out I will appreciate

**FDibbins** · 03-31-2014, 01:28 AM

Try these 2 ARRAY formulas copied down...

P4=INDEX($D$4:$K$38,MATCH($M4&$T$6,$B$4:$B$38&$C$4:$C$38,0),MATCH($R$15,$D$1:$K$1,0))
Q4=INDEX($D$4:$K$38,MATCH($M4&$T$6,$B$4:$B$38&$C$4:$C$38,0),MATCH($R$16,$D$1:$K$1,0))

...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.

**deanusa** · 03-31-2014, 10:21 AM

Thank you !!

**FDibbins** · 03-31-2014, 02:40 PM

Glad I could help

index match issue

LinkBack

Thread Tools

Rate This Thread

Display

index match issue

Re: index match issue

Re: index match issue

Re: index match issue

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index and Match - VBA - Issue

[SOLVED] index match issue

index match issue

index, match issue

Index/Match issue

Bookmarks

Bookmarks

Posting Permissions