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.
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.
Regards,
Fareed
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.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
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)))
Last edited by Bo_Ry; 12-24-2020 at 11:38 AM.
Thanks for the dynamic array formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks