index match issue

[avandever]

i am trying to return a value in a table that matches the same column and row titles. i am assuming the easiest way is to use the index match function. does anyone see anything with my formula?

=IFERROR(INDEX(pur!$H$7:$V$1734,MATCH(combined!$C7,pur!$C$7:$C$1734,0),MATCH(combined!$H4,pur!$H$4:$U$4,0)),"")

green is the area where all the data is on the other sheet, orange is the column on the left i am trying to match, and blue is the row title i am trying to match. thoughts? thanks.

 

[barry houdini]

Looks OK to me....are you getting blanks (from IFERROR) when you don't expect them?

Try each MATCH function separately, is either one returning #N/A?

 

[avandever]

it is just returning 0, not even a blank from the iferror "". I thought maybe it had to deal with the formatting on some of the columns(some were values/numbers and some were formulas) but i made everything the same format and it still returns 0.

 

[barry houdini]

What result do you expect? You'd get zero if the intersect cell is blank.......

 

[avandever]

there are a lot of cells that are blank(0) but when i fill down and across the cells that should match up and return >0 values still return a 0.

 

[Scott R]

I like to troubleshoot INDEX/MATCH on the formula bar. Drag through the MATCH(...) formula text in the formula bar, and hit F9 to see intermediate result.

Or drag through the entire INDEX(...) formula text, copy, and paste into a Goto dialog box (F5).

 

[avandever]

okay, i know the problem but now i have to fix it. it has to deal with the cells that i am locking down and when i am filling in across and down it is trying to match wrong cells.