Index Match Issue

[jharvey87]

Hi Everyone,

Can someone let me know what I'm doing wrong here. I'm trying to do an index match function and I'm returning an error. Here is my formula:
=INDEX('Overhead Budgets'!$E$4:$E$197,MATCH($C$5&$C$6,'Overhead Budgets'!$C$4:$C$197&'Overhead Budgets'!$D$4:$D$197,0))

The two criteria I'm looking at are in cells C5 and C6. The data to match this too is in the Overhead Budgets sheet. For cell C5, the data to match it to is in C3:C197. In cell C6, the data to match is in D4:D197. The return result (the budget) i want to return is in column E.

Any idea where I'm going wrong with my formula?

[Sc0tt1e]

'Overhead Budgets'!$C$4:$C$197&'Overhead Budgets'!$D$4:$D$197

Shouldn't this be 'Overhead Budgets'!$C$4:$D$197?

[FDibbins]

=INDEX('Overhead Budgets'!$E$4:$E$197,MATCH($C$5&$C$6,'Overhead Budgets'!$C$4:$C$197&'Overhead Budgets'!$D$4:$D$197,0))

If you combine ranges like that in teh MATCH, you need to enter it as an ARRAY formula...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.

@ Sc0ttie MATCH only takes a single column.row for the range

[jharvey87]

That was it. Thanks!

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)