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

p45cal · #1 04-14-2022, 04:11 AM

it looks like you missed one close bracket:

Code:

=IFERROR(IF(VLOOKUP(A219,Form1!$A:$AK,11,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,14,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,15,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,16,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,17,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,18,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,19,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,20,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,34,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,35,FALSE)&""="",IF(VLOOKUP(A219,Form1!$A:$AK,36,FALSE)&""="",VLOOK UP(A219,Form1!$A:$AK,37,FALSE),VLOOKUP(A219,Form1!$A:$AK,36,FALSE)),VLOOKUP(A219,Form1!$A:$AK,35,FALSE)),VLOOKUP(A219,Form1!$A:$AK,34,FALSE)),VLOOKUP(A219,Form1!$A:$AK,20,FALSE)),VLOOKUP(A219,Form1!$A:$AK,19,FALSE)),VLOOKUP(A219,Form1!$A:$AK,18,FALSE)),VLOOKUP(A219,Form1!$A:$AK,17,FALSE)),VLOOKUP(A219,Form1!$A:$AK,16,FALSE)),VLOOKUP(A219,Form1!$A:$AK,15,FALSE)),VLOOKUP(A219,Form1!$A:$AK,14,FALSE)),VLOOKUP(A219,Form1!$A:$AK,11,FALSE)), "")

Why the:

Form1!$A:$AK,11,FALSE)&""="",IF(VLO
and not just:
Form1!$A:$AK,11,FALSE)="",IF(VLO

Code:

=IFERROR(IF(VLOOKUP(A219,Form1!$A:$AK,11,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,14,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,15,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,16,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,17,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,18,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,19,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,20,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,34,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,35,FALSE)="",IF(VLOOKUP(A219,Form1!$A:$AK,36,FALSE)="",VLOOK UP(A219,Form1!$A:$AK,37,FALSE),VLOOKUP(A219,Form1!$A:$AK,36,FALSE)),VLOOKUP(A219,Form1!$A:$AK,35,FALSE)),VLOOKUP(A219,Form1!$A:$AK,34,FALSE)),VLOOKUP(A219,Form1!$A:$AK,20,FALSE)),VLOOKUP(A219,Form1!$A:$AK,19,FALSE)),VLOOKUP(A219,Form1!$A:$AK,18,FALSE)),VLOOKUP(A219,Form1!$A:$AK,17,FALSE)),VLOOKUP(A219,Form1!$A:$AK,16,FALSE)),VLOOKUP(A219,Form1!$A:$AK,15,FALSE)),VLOOKUP(A219,Form1!$A:$AK,14,FALSE)),VLOOKUP(A219,Form1!$A:$AK,11,FALSE)), "")

djlw84 · #2 04-14-2022, 01:36 PM

Please see attached sample workbook.

Form1 is where users forms requests are being recorded.Sheet 2 is where I want the formula to be applied (in Column H). I have put the desired result in the column.

I want to lookup the ID number in Column A of Sheet 2 against Form1 and return the client name from one of the 12 cells that could contain a client name (only one of these 12 cells will ever have a value in).

As I mentioned before, the formula was working fine before I tried to add the four new column/cell references (AH, AI, AJ and AK).

Hopefully this makes sense - happy to clarify further if needed!

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