To Skip Empty Cells & blank Spaces and Capture Values

Hi Excel Users,

Please help in getting the values from a particular column as the column has blank cells and cells with blank spaces which makes difficult to lookup the correct value. Sample file attached for reference.

Some of the values in column C are stored as TEXT that look like a number, others as real numbers.

Select column C. Data/Text to columns/Finish. In J5:

=IFERROR(INDEX($C$3:$C$4070,MATCH(1,INDEX((($A$3:$A$4070=$H5)*($C$3:$C$4070<>" ")*($B$3:$B$4705="MS")),0),0)),"")

NOTE there are spaces in the empty cells. See red bit of formula. If you are doing much more with the data, you'd be better off deleting them. So, instead of text to columns you could use:

=IFERROR(TRIM(C3)+0,"") in D2 copied down. then select column D copy/paste VALUES and then delete column C. At that point:

=IFERROR(INDEX($C$3:$C$4070,MATCH(1,INDEX((($A$3:$A$4070=$H5)*($C$3:$C$4070<>"")*($B$3:$B$4705="MS")),0),0)),"")

will work. In the attached file, I did NOT remove the spaces and used the first listed formula.

Thanks Glenn. This is very much clear. Instead of adding an additional column and then turning to values. Is there any possibility to use the trim function along with the Index Match function to get the desired result.

No reason why not... but the BEST option is to kill them right from the start, as described in the 2nd half of my post. If you REALLY want to keep them (I advise AGAINST keeping them) see the file.

Maybe try

=FILTER($A$3:C$4070,LEN(TRIM(C3:C4070)))

Thanks for the dynamic array formula.