CODEWITHSUNDEEP
excelgoogle-sheets
thefragileomen
thefragileomen

Reputation: 1547

Ignoring Blanks in an index / rank in Google Sheets

I have the below formula which looks up a value of a cell in a column (B3:B14), based on a column of random (rand()) values (J3:J14)-

=INDEX(Wordlist_Upper!$B$3:$B$14, IF(Wordlist_Upper!$B$3:$B$14<>"", RANK(Wordlist_Upper!J7, Wordlist_Upper!$J$3:$J$14)))

This works fine however it sometimes returns blank values as some of the content of cells B3:B14 may be blank.

Is there a way to instruct it to only return values if the cells contain something (ie. ignore blank cells within the range B3:B14)?

Thanks

UPDATE: I've tried adding IF(Wordlist_Upper!$B$3:$B$14<>"", in the middle, but it still returns blanks

Upvotes: 0

Views: 2586

Answers (1)

Tedinoz
Tedinoz

Reputation: 7949

You were on the right track, but your IF evaluation is invalid, which has a knock-on effect on your INDEX statement.

Try this instead, or at least a variant tailored to your data.

=if((isblank(index($B$3:$B$14,rank(J7,$J$3:$J$14,0))))=true,"cell is empty",isblank(index($B$3:$B$14,rank(J7,$J$3:$J$14,0))))

This nests several components. The main different compared to your formula (besides syntax) is the order of the nesting.
These are the components: 

1 - index($B$3:$B$14,rank(J7,$J$3:$J$14,0))

This gets a cell value from column B. The row offset is the rank (an integer) of cell J7 among the numbers in column J. 

2 - isblank(index($B$3:$B$14,rank(J7,$J$3:$J$14,0)))

This evaluates whether the cell obtained by the INDEX component is empty of not. If the cell is blank, then the formula will return TRUE; if the cell is not empty, then the formula will return FALSE. 

3 - if(isblank()=TRUE,"cell is empty", index())

The last component is the IF statement. To paraphrase:
If the cell in column B is blank (i.e. isblank() = TRUE), then display some text saying the cell is empty, otherwise the cell isn't empty (i.e. isblank()=FALSE) so return the value generated by the INDEX statement.

Obviously you should substitute your alternate value in place of my "cell is empty" string. To be honest, I couldn't figure out what you wanted to do when the cell was empty, otherwise I would have completed the formula.

Upvotes: 2

Related Questions