IF and ISNUMBER problem

[Ekstamm]

Im struggling with a formula and I can not find the answer on google.

I have a VLOOKUP funktion that I want to write out depending if some cells have values or not.

the VLOOKUP get a watt(electrical) value for a fixture. and I want this to depend on how many fixtures I patch.

so:

G8 will get VLOOKUP(F8;'Fixture List'!A:B;2;0)
But I want it to write out The VLOOKUP value as 0,00 if J8+L8+N8+P8 is blank.
And when Only J8 has a value I want it to write VLOOKUP value as it is
When J8+L8 has a value I want VLOOKUP value *2
when J8+L8+N8 has a value I want VLOOKUP value *3
when J8+L8+N8+P8 has a value I want VLOOKIP vale *4

I get it to work for only J8 with formulra:
=IF(ISNUMBER(J8);VLOOKUP(F8;'Fixture List'!A:B;2;0);0)

Help me please. I can not get around it.

 

[Ekstamm]

I solved the problem, but It must be an easier way to do it.
This is the formula I used to get it to work:

=IF(ISNUMBER(P8/P8/N8/L8/J8);4*VLOOKUP(F8;'Fixture List'!A:B;2;0);IF(ISNUMBER(P8/N8/L8/J8);3*VLOOKUP(F8;'Fixture List'!A:B;2;0);IF(ISNUMBER(N8/L8/J8);2*VLOOKUP(F8;'Fixture List'!A:B;2;0);IF(ISNUMBER(J8);VLOOKUP(F8;'Fixture List'!A:B;2;0);0))))

 

[T. Valko]

Im struggling with a formula and I can not find the answer on google.

I have a VLOOKUP funktion that I want to write out depending if some cells have values or not.

the VLOOKUP get a watt(electrical) value for a fixture. and I want this to depend on how many fixtures I patch.

so:

G8 will get VLOOKUP(F8;'Fixture List'!A:B;2;0)
But I want it to write out The VLOOKUP value as 0,00 if J8+L8+N8+P8 is blank.
And when Only J8 has a value I want it to write VLOOKUP value as it is
When J8+L8 has a value I want VLOOKUP value *2
when J8+L8+N8 has a value I want VLOOKUP value *3
when J8+L8+N8+P8 has a value I want VLOOKIP vale *4

I get it to work for only J8 with formulra:
=IF(ISNUMBER(J8);VLOOKUP(F8;'Fixture List'!A:B;2;0);0)

Help me please. I can not get around it.

Maybe something like this...

=COUNTA(J8;L8;N8;P8)*VLOOKUP(F8;'Fixture List'!A:B;2;0)

 

[Ekstamm]

Well that was a lot easier. How do I get the G8 Cell to be blank if value is #N/A?

 

[pgc01]

Hi
Welcome to the board

Please always post your excel version.

If you have excel 2007 or later, check IFERROR()

 

[T. Valko]

Well that was a lot easier. How do I get the G8 Cell to be blank if value is #N/A?

Try one of these...

This one will work in EVERY version of Excel:

=IF(ISNA(MATCH(F8;'Fixture List'!A:A;0));"";COUNTA(J8;L8;N8;P8)*VLOOKUP(F8;'Fixture List'!A:B;2;0))

This one will work in Excel 2007 and later:

=IFERROR(COUNTA(J8;L8;N8;P8)*VLOOKUP(F8;'Fixture List'!A:B;2;0);"")