I am trying use a referenced cell similar to a Vlookup, but the data I'm looking up for said reference cell, I want to only return the non-blank data cells, because my data contains blank cells.
My sheet is more complicated but, if I could get the example shown below to work I could make it work for my bigger, more complex spreadsheet.
I search around and was able to find an array formula that is working for me...but only if the data contains values. And my data contains both values/non-values. I have struggled to find a formula that will work in both cases.
I have attached the example. I have my data in A2:B7. Column A2:A7 are my labels/references, Column B2:B7 are my data inputs (includes the blank) plus values/non-values.
Cell A12 & A13 has the matching references from A2:A7 I want to lookup. My formula is successful in B12 for referencing cell A12 because the input in B2:B7 is a value. My formula is NOT successful in B13 referencing cell A13 because the input in B2:B7 is a Non-value. I am using the MAX function, which is why Im guessing it doesn't like non-value data.
My formula is =IF(MAX(IF(($A$2:$A$7=A12)*($B$2:$B$7<>0),$B$2:$B$7))=0,"",MAX(IF(($A$2:$A$7=A12)*($B$2:$B$7<>0),$B$2:$B$7))). Of Course with an Array.
Bookmarks