Excel VLOOKUP - Return cells that are not blank from range of specified cells

ArviLaanemets · #2 04-13-2022, 11:56 PM

The first formula has 7 nested IF's, what is a max number of nesting allowed!

Workarounds:
a) Use 'subnesting', i.e. use an IF() to divide your single-column conditions into 2 or more groups of conditions, and then use nested IF's <like = IF(StartCondition, IF(... subnest 1 ...), IF(... subnest 2 ...))>;
b)Use formula instead like

Code:

(VLOOKUP(...)&""="")*(VLOOKUP(...)&""="")*...*VLOOKUP(...)
+
(VLOOKUP(...)&""="")*(VLOOKUP(...)&""="")*...*VLOOKUP(...)
+
...

c) Use helper column in your table to calculate the number of return column of VLOOKUP. Your formula will be like

Code:

=VLOOKUP(A219,Form1!$A:$X,HelperColumnValue,FALSE)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
have a formula that combines two cells return a zero instead of a blank cell	Kubi	Excel	3	08-07-2017 11:52 PM
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug)	slaycock	Word VBA	0	02-18-2017 07:00 AM
Vlookup fill down blank cells in column	mbesspiata	Excel Programming	8	09-26-2016 04:44 AM
Excel Formula: return a range of cells that match	tinfanide	Excel	4	08-30-2014 07:03 AM
Copying text range of cells to different cells adds an extra line	jpb103	Word VBA	2	07-23-2014 12:22 PM