Question about IF, ISBLANK and VLOOKUP

[JamieP89]

Hi all,

So I’m starting to get better with excel and becoming more comfortable coming up with solutions myself which is great.

I have however stumbled into a problem.

I’m trying to nest a ISBLANK into an IF statement while using 3 VLOOKUPS to look accords three separate columns for potential data input (only 1 column would ever contain data)

I want to be able to show a blank cell unless one of the 3 columns is filled in but every time I try this, I get “too few many arguments” comments thrown at me.

I can get the logic to work with one VLOOKUP but just can’t seem to make it work with more than one…

Hopefully someone can explain how to make this worth with an example of how it should read !

 

[Max1616]

For nested vlookups, I would use IFERROR instead of ISBLANK. Something like this:

=IFERROR(IFERROR(IFERROR(VLOOKUP(A1,H:I,2,FALSE),VLOOKUP(A1,K:L,2,FALSE)),VLOOKUP(A1,N:O,2,FALSE)),"")

I'm unsure why you're having a problem, I could probably provide more help if you provide some screenshots of your data, and the formula you are using that is not working properly.

Good Luck!

 

[Peter_SSs]

Rather than screenshots, what about a few sets of sample data and expected results with XL2BB?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Peter_SSs]

Reading over your question again, I am wondering if this is the sort of thing you are after?

22 06 11.xlsm
	A	B	C	D	E	F	G
1		Value 1	Value 2	Value 3		Lookup value	Result
2	a		cat			b	dog
3	b			dog		c
4	c					a	cat
5	d	mouse
Lookup

Cell Formulas
Range	Formula
G2:G4	G2	=CONCAT(INDEX(B$2:D$5,MATCH(F2,A$2:A$5,0),0))

 

[JamieP89]

For nested vlookups, I would use IFERROR instead of ISBLANK. Something like this:

=IFERROR(IFERROR(IFERROR(VLOOKUP(A1,H:I,2,FALSE),VLOOKUP(A1,K:L,2,FALSE)),VLOOKUP(A1,N:O,2,FALSE)),"")

I'm unsure why you're having a problem, I could probably provide more help if you provide some screenshots of your data, and the formula you are using that is not working properly.

Good Luck!

Thanks for the suggestion.

I'm not sure what led me to ISBLANK to be honest, I've used IFERROR before, so unsure why...

Something in the back of my mind is telling me it was because the value using the current formula i had (which was an IFERROR(IF() statement was giving me £0 as a result and not an "error" so was trying to use ISBLANK???

I've just changed the entire formula to an IFERROR(IFS() statement as it's more helpful anyway as I can condition three columns now knowing that not only is the VLOOKUP cell blank, but I am looking accross the 3 columns which is what I wanted

There's likely far more graceful ways to do this that having to write three vlookups, but i don't yet know what that is

THANK YOU

 

[Peter_SSs]

I've just changed the entire formula to an IFERROR(IFS() statement ...

There's likely far more graceful ways to do this ...

Could you post your current working formula?

 

[JamieP89]

Could you post your current working formula?

Sure.

This is an example of exactly how it is written, just not the same ranges and cells used (it's on my work computer and quicker to just re write it )

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1
2
3
4
5			=IFERROR(IFS(VLOOKUP(A1,$A:$AI,2,FALSE),VLOOKUP(A1,$A:$AI,2,FALSE),VLOOKUP(A1,$A:$AI,3,FALSE),VLOOKUP(A1,$A:$AI,3,FALSE),VLOOKUP(A1,$A:$AI,4,FALSE),VLOOKUP(A1,$A:$AI,4,FALSE)),"")
6
7
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
C5	C5	=IFERROR(IFS(VLOOKUP(A1,$A:$AI,2,FALSE),VLOOKUP(A1,$A:$AI,2,FALSE),VLOOKUP(A1,$A:$AI,3,FALSE),VLOOKUP(A1,$A:$AI,3,FALSE),VLOOKUP(A1,$A:$AI,4,FALSE),VLOOKUP(A1,$A:$AI,4,FALSE)),"")

 

[Peter_SSs]

This is an example of exactly how it is written, just not the same ranges and cells used

So, I'm not really sure if that is showing what I wanted to know if it may not be the actual formula.

In what you have posted, using a VLOOKUP for the value in A1, in column A would always find it in A1. In that case it seems your formula is just looking for the first number (if any) in B1:D1.
If that was so, this would do the same job.

=INDEX(FILTER(B1:D1,ISNUMBER(B1:D1),""),1)

 

[JamieP89]

So, I'm not really sure if that is showing what I wanted to know if it may not be the actual formula.

View attachment 66924

In what you have posted, using a VLOOKUP for the value in A1, in column A would always find it in A1. In that case it seems your formula is just looking for the first number (if any) in B1:D1.
If that was so, this would do the same job.

=INDEX(FILTER(B1:D1,ISNUMBER(B1:D1),""),1)

Exactly that. I’m a real work scenario, I’m looking up a project name typed into one excel, and looking it up in another excel to then provide either column, 2,3,4 which ever has the number in.

I have a similar for 3 columns but any of the 3 columns have a value at a given time so have it setup up that the final (3rd column) is the prioritised number taken.

In this case I know only one of the 3 columns will actually be filled but need it to be looking in each three as never know which one will be filled in.

I’ll have a look at INDEX but though XLOOKP was designed to replace this. (Not familiar with INDEX though.

 

[Peter_SSs]

I’m looking up a project name typed into one excel, and looking it up in another excel

So in the formula you quoted above, the A1 and the $S:$AI are actually not on the same worksheet as indicated by the formula, but are on different worksheets?

If so what about this then?

JamieP89.xlsm
	A	B	C	D
1
2	a	2
3	b			33
4	c
5	d		15
Sheet1

JamieP89.xlsm
	A	B
1
2	b	33
3	d	15
4	x
5	c
6	a	2
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(--CONCAT(FILTER(Sheet1!B$2:D$5,Sheet1!A$2:A$5=A2,"")),"")