Ignore blank cells when using Index and Match formula

[Calaquendi]

Hello,

I need to read out information from a database using the Index and Match formula combined. In my example data I have 1 criteria giving away the row (company name) and 2 criterias giving away the columb (year and season). This is the formula I am using:

Please Login or Register  to view this content.

In rare cases, one company (columb A) is shown two times, with the first entry blank. When the index formula hits such a blank entry, it returns zero. How can I make the Index formula search on until it finds the next entry that matches the criteria which has a value in it?

There is one limitation: Some entries in the database are asteriks (***) and therefore not numeric. I tried using the SUMPRODUCT formula, which seems to give me what i want, but it only returns #NV in every case because of these non numeric entries.

See attached my example data.

Thank you!

[Pete_UK]

This worked for me in B12:

=SUMPRODUCT($B$3:$E$7,($A$3:$A$7=A12)*($B$1:$E$1=$B$10)*($B$2:$E$2=$B$11))

though you might need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

[bebo021999]

Try

IFERROR($B$3:$E$7+0,0)

instead of $B$3:$E$7, to avoid error, change it into zero value.

B12 should be:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[bebo021999]

=SUMPRODUCT($B$3:$E$7,($A$3:$A$7=A12)*($B$1:$E$1=$B$10)*($B$2:$E$2=$B$11))

Thanks. It remind me using SUMPRODUCT(XXX,YYY) in this case.

[Calaquendi]

=SUMPRODUCT($B$3:$E$7,($A$3:$A$7=A12)*($B$1:$E$1=$B$10)*($B$2:$E$2=$B$11))

Oh wow, it really does work! Thank you. Is there a way to also retrieve non numeric data like the asteriks (***) with that formula? I tested it and it returns 0 for those. I thought, maybe I can wrap the SUMPRODUCT formula in an IF formula, for example like this (in B12):

Please Login or Register  to view this content.

But in case I have zeros in the database, it would transform these into *** as well. Do you have an idea for that too?

[Calaquendi]

B12 should be:

Please Login or Register  to view this content.

I will stick to Pete's easier solution, but thanks alot for your effort!

[Pete_UK]

SUMPRODUCT just returns numeric values (or zero), but *** is a text value, so I don't think you can do it that way. Perhaps if you changed *** to -1 (using Find & Replace), then you could trap it that way.

Hope this helps.

Pete