Why is my index/match returning a 0 sometimes, and an error other times?

[DaraMurray]

Hi there!

I am working with a large dataset, pulling from multiple sources. I have used the combination of index and match many times in this exact situation for different projects and in other areas of this same workbook with no problems. The formula returns an error when the cell it's drawing from is blank and returns the correct values for each cell... or at least no zeros.

Here's the problem: I am pulling four columns from Sheet1 of ratings and then calculating an average with those ratings in Sheet2. I'm repeating this process using index and match to move scores from multiple tabs (representing different indicators/scales) all into one place. Because I then calculate averages, it is critical that if a score is blank (not all raters score all indicators) that is returns a blank NOT a zero. It is not possible for there to be a score of zero.

Example of what I'm pulling from:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Indicator A[/TD]
[TD]Indicator B[/TD]
[TD]Indicator C[/TD]
[TD]Indicator D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I would then go on to average these together. As you can see, Index/match returning a zero for column A when really column A is just BLANK is a big issue.

Here's the formula I'm using:

=INDEX(Sheet1!$H$2:$H$243,MATCH(G2,Sheet1!$E$2:$E$243,0))

Where Sheet1 H is the value I want to pull (a score ranging from 1 to 4), G2 is the ID# in Sheet2, and Sheet1 E contains the ID#s as well.

This has only been a problem for me working with these specific scores from this particular sheet. I have successfully used this formula to do the same thing with different numbers with different sheets as the source.

Here's what I've tried so far:

• Making sure all the columns in question are the same format (have tried text and general)
• Copying all the values from the problem sheet into a new sheet by just pasting VALUES (in case formatting or something was the issue)
• Redoing the formula from scratch (aka not just copying it from the column next to it and changing the column number)
• copying the information again from my 6 source sheets (I copy and paste just values).

Any insight? I've never run into this issue before!

Thanks,
Dara

EDIT: I think it might have something to do with how my ID#s are stored. Though they are all formatted as "general" some have the error that it is a number stored as text, and some numbers do not have this error. When I sorted them by ID#, the numbers stored as text were sorted under the other numbers without the error... even though this is not correct numerical order. What a mystery!!!

 

[Jonmo1]

To turn 0 results into "" try

=IFERROR(1/(1/INDEX(Sheet1!$H$2:$H$243,MATCH(G2,Sheet1!$E$2:$E$243,0))),"")


If it's returning 0 when you think it should be returning 3, it's likely there is another match for G2 earlier in the E column on Sheet1.

 

[DaraMurray]

Update: It looks like the only times an error is being returned is when this ID is missing from the dataset, so zeros are coming back for all blanks.

 

[DaraMurray]

Thanks that was my mistake, it is only returning a zero for blanks.

I tried your formula, but it isn't quite meeting my needs. When I include both the 1/(1/ I get "The formula you typed contains an error." When I remove 1 of the 1/ it turns my 3 responses into .3 repeating. When I remove both 1/(1/ I still get zeros instead of blanks or #N/A.

 

[Jonmo1]

Works fine for me.

Did you copy/paste the formula from my post into your sheet, or did you manually add the 1/(1/ ?
There are also added ) symbols as well.

Just copy it directly from my post.

 

[DaraMurray]

It worked! Would you mind explaining the WHY behind it, so I can better understand how this worked and how I might fix it myself in the future?

And THANK YOU!

 

[Jonmo1]

You're welcome.

Not at all, it's actually avery usefull trick, with very simple math.

Take ANY number, say 10 to make the example simple.
Divide 1 by that number.
1/10 = 0.10

Now divide 1 by that result
1/0.10= 10


Basically ANY number, apply this math 1/(1/x) will return the same original number.
Unless the value is 0 or an error like #N/A, that will return an error, then trapped by IFERROR.

 

[DaraMurray]

Incredible! Thank you so much, you're right it's so simple when it's explained. You've really saved me on this one! Thanks agin!