2D Index match match & error trapping

Hi Guys,

Not sure if the topic title is quite right.

I am trying to create a 2d index match match lookup formula. I have to account for potential blank vlaues that would normally spit out a nil.

I'm using the following formula:

=IFERROR(IF(INDEX($C$4:$N$33,MATCH($Q11,$B$4:$B$33,0),MATCH(R$9,$C$3:$N$3,0))=0,"",INDEX($C$4:$N$33,MATCH($Q11,$C$3:$N$3,0),MATCH(R$9,$C$3:$N$3,0))),"")

However, each cell contains a value and so it should return the values from the array? Yet when i complete the formula it just returns a blank value. I've created a mini adjacent table and I want to return all of the values yet retain the other part of the formula that accounts for blanks - if index and match = 0 and also the iferror what am I doing wrong?

Thanks so much!

So you want 0's to be shown as blank?

Try this:

R10 =IFERROR(1/(1/INDEX($C$4:$N$33,MATCH($Q19,$B$4:$B$33,0),MATCH(R$18,$C$3:$N$3,0))),"")

Now if you delete cell C6, you will see that R10 turns blank instead of showing 0.

I interpreted it as you want blanks to be shown as 0's so either way you'll have a formula that works.

=IFERROR(INDEX($C$4:$N$33,MATCH($Q10,$B$4:$B$33,0),MATCH(R$9,$C$3:$N$3,0)),"")

This was a fun exercise to learn from, thanks for posting,
LearningByError



Edit: Please mark this as solved if we have in fact solved your problem!

Thanks so much guys. I haven't explained it very well.

If I retained the row for brooks candies but removed all the sales values in each of the rows in the main table array, I want it to display a blank cell from cells R19 to U19 in the lookup return table. Display blank values if true, but return values from tablle array if false.

It's actually going to be used for a mail merge so the formulas need to sit in each of the cells as blank and only display once a 4-code unique lookup value is entered from the table array

Thanks so much