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 !

 

[JamieP89]

Sorry perhaps I'm not being clear enough.

Book10
	A	B	C
1	Project Name
2	Project 1	3
3
4
Sheet2

Cell Formulas
Range	Formula
B2	B2	=IFERROR(IFS(VLOOKUP(Sheet2!A2,Sheet1!A:D,4,FALSE),VLOOKUP(Sheet2!A2,Sheet1!A:D,4,FALSE),VLOOKUP(Sheet2!A2,Sheet1!A:D,3,FALSE),VLOOKUP(Sheet2!A2,Sheet1!A:D,3,FALSE),VLOOKUP(Sheet2!A2,Sheet1!A:D,2,FALSE),VLOOKUP(Sheet2!A2,Sheet1!A:D,2,FALSE)),"")

Book10
	A	B	C	D
1	Name	First number	Second Number	Third Number
2	Project 1	1	2	3
3
Sheet1

This is what I am doing in reality. I am looking (for examples purchase on another sheet (would usually be a workbook) and asking it to check the columns 4,3,2 (it is then superseeding the previous input number i.e. if I were to put a 1 in column 2, it would give two, but if I then kept two in there, and put a 1 in column 3 it would give me 1 until the final column gets data entry and am left with the final number.

As I say, not so much needed in the initial issue i was having but keen to explore if there is a better way to achieve this long winded formula (no doubt slowing my excel down)

Sorry I can't presume to understand your formula above.

but if it makes it do what I have done there, keen to learn!

 

[Peter_SSs]

This is what I am doing in reality.

I was using your original information.

(only 1 column would ever contain data)

JamieP89.xlsm
	A	B	C	D
1	Name	First number	Second Number	Third Number
2	Project 1	5	1	3
3	Project 2	6
4	Project 3
5	Project 4	3	22
Sheet1

JamieP89.xlsm
	A	B
1	Project Name	Last Num
2	Project 1	3
3	Project 3
4	Project 4	22
5	Project 2	6
6	Project 8
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=IFNA(LOOKUP(9E+307,FILTER(Sheet1!B$2:D$5,Sheet1!A$2:A$5=A2,NA())),"")

 

[JamieP89]

I was using your original information.

JamieP89.xlsm
	A	B	C	D
1	Name	First number	Second Number	Third Number
2	Project 1	5	1	3
3	Project 2	6
4	Project 3
5	Project 4	3	22
Sheet1

JamieP89.xlsm
	A	B
1	Project Name	Last Num
2	Project 1	3
3	Project 3
4	Project 4	22
5	Project 2	6
6	Project 8
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=IFNA(LOOKUP(9E+307,FILTER(Sheet1!B$2:D$5,Sheet1!A$2:A$5=A2,NA())),"")

Thanks for this, would you be able to breakdown how this formula is working as never seen this sort of formula.

 

[Peter_SSs]

would you be able to breakdown how this formula is working

Using my sample data and the formula for Project 4

On Sheet1 it FILTERs B2:D5 and returns everything where column A = Project 4. That returns a single row with these three values

LOOKUP then looks for 9E307, a very big number (9 followed by 307 zeros). A feature of LOOKUP is that if the number being looked for cannot be found and there is nothing bigger than the number being looked for, the function returns the last number that is does find. In this case the last number found in that row is 22.

If the project name cannot be found in Sheet1 or the project name can be found but LOOKUP cannot find any numbers at all in the row then the lookup returns #NA. The IFNA function converts such results to a null string as in rows 3 and 6 of my sample.

Much simpler than all those VLOOKUPS, particularly if you had lots of columns to look in instead of just 3.

 

[JamieP89]

Using my sample data and the formula for Project 4

On Sheet1 it FILTERs B2:D5 and returns everything where column A = Project 4. That returns a single row with these three values
View attachment 67009
LOOKUP then looks for 9E307, a very big number (9 followed by 307 zeros). A feature of LOOKUP is that if the number being looked for cannot be found and there is nothing bigger than the number being looked for, the function returns the last number that is does find. In this case the last number found in that row is 22.

If the project name cannot be found in Sheet1 or the project name can be found but LOOKUP cannot find any numbers at all in the row then the lookup returns #NA. The IFNA function converts such results to a null string as in rows 3 and 6 of my sample.

Much simpler than all those VLOOKUPS, particularly if you had lots of columns to look in instead of just 3.

I see! And how is it identifying project 4 over day project 3?

I’m so used to having to call out a name to look for to match like In VLOOKUP so keen to understand the logic of how it’s matching the project names

 

[Peter_SSs]

And how is it identifying project 4 over day project 3?

The formula in B4 of Sheet2 is
=IFNA(LOOKUP(9E+307,FILTER(Sheet1!B$2:D$5,Sheet1!A$2:A$5=A4,NA())),"")
A4 in Sheet2 is "Project 4" so the identification of the correct row from Sheet1 is made by the red part of the formula.

 

[JamieP89]

The formula in B4 of Sheet2 is
=IFNA(LOOKUP(9E+307,FILTER(Sheet1!B$2:D$5,Sheet1!A$2:A$5=A4,NA())),"")
A4 in Sheet2 is "Project 4" so the identification of the correct row from Sheet1 is made by the red part of the formula.

Thanks so much for the help around this. Now I need to look to better understand FILTER (Can FILTER, filter out multiple points to provide a result??)

I was creating a formula today using COUNTIF and needed to only count certain record names and ended up settling on a SUM(COUNTIF to avoid SPILL.

 

[Peter_SSs]

Can FILTER, filter out multiple points to provide a result??

Most likely 'yes' but would need to know details of just what you have and what you are trying to achieve to advise further.

 

[JamieP89]

Most likely 'yes' but would need to know details of just what you have and what you are trying to achieve to advise further.

Okay well I will post a new topic as technically you’ve answered my question and I’ve marked as answered

 

[Peter_SSs]

Okay well I will post a new topic as technically you’ve answered my question and I’ve marked as answered

Good plan.