Vlookup needs to be blank if there is specific data

[Brentsa]

Good day I have the following formulas in my excel sheet:

H6=> =VLOOKUP($E6,'3 Nov 21'!$B:$V,14,)
I6=> =IF(H6="","",(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24)
J6=> =VLOOKUP($E6,'3 Nov 21'!$B:$V,20,)
K6=> =IF(J6="","",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24)
L6=> =ROUNDDOWN(((H$2-$F6)/365.25),0)
M6=> =IF(AND(H6=0,J6=0),0,IF(H6="",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24,(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24))
N6=> =MATCH(M6,IF(H6=0,('8km Points'!$K$4:$K$234),('5km Points'!$K$4:$K$185)),1)+3
O6=> =HLOOKUP(L6,(IF(H6=0,'8km Points'!$M$3:$V$234,'5km Points'!$M$3:$V$185)),(N6-2))

My problem is that as soon as I have 0.00.00 in column 14 of sheet 3 Nov 21 it creates a #value error in cells I6 which in return creates the #value error in these cells as well: M6;N6;O6.

How can I fix I6 to reflect it to be blank if it encounters 0.00.00?

At the same time the same principle can be used for H6 and J6

 

[Joe4]

One easy way may be to simply use the IFERROR function to ignore any errors, and return whatever you want in those instances, i.e.
=IFERROR(your original formula, "")
which basically says, if your formula returns as error, return nothing (""), otherwise return the calculation from the formula.

 

[Brentsa]

One easy way may be to simply use the IFERROR function to ignore any errors, and return whatever you want in those instances, i.e.
=IFERROR(your original formula, "")
which basically says, if your formula returns as error, return nothing (""), otherwise return the calculation from the formula.

the =iferror does not solve the issue for cell I6 and thus m6 does not work

 

[Joe4]

the =iferror does not solve the issue for cell I6 and thus m6 does not work

I am confused.
Your M6 formula does not even look at cell I6. It looks at H6 and J6.

With all your interactive formulas, it is hard to visualize everything without actually seeing what your data looks like.
Remember, you have access to see all of that. but we do not.
In these cases, it is often helpful if you post some data samples, so we can see exactly what the data you are working with looks like.

 

[Brentsa]

I am confused.
Your M6 formula does not even look at cell I6. It looks at H6 and J6.

With all your interactive formulas, it is hard to visualize everything without actually seeing what your data looks like.
Remember, you have access to see all of that. but we do not.
In these cases, it is often helpful if you post some data samples, so we can see exactly what the data you are working with looks like.

Sorry you are right I got confused:
i6 links to h6;
k6 links to j6

m6 links to h6 and j6.

So if H6 vlookup result is 0.0.00 m6 does not work and shows #value error. if I delete the formula in h6 then it works perfectly but I need the formula in h6. How can i change the vlookup in H6 to return a blank result (empty cell) if result is 0.0.00?

 

[Joe4]

Sorry you are right I got confused:
i6 links to h6;
k6 links to j6

m6 links to h6 and j6.

So if H6 vlookup result is 0.0.00 m6 does not work and shows #value error. if I delete the formula in h6 then it works perfectly but I need the formula in h6. How can i change the vlookup in H6 to return a blank result (empty cell) if result is 0.0.00?

I think it would be most beneficial for us to see a sample of what the data in the sheet you are looking up looks like, especially from column O of your '3 Nov 21' sheet, as that appears to be the column you are returning from.

 

[Brentsa]

I think it would be most beneficial for us to see a sample of what the data in the sheet you are looking up looks like, especially from column O of your '3 Nov 21' sheet, as that appears to be the column you are returning from.

TIME TRIALS 03-11-2021

SURNAME

NAME

CELL

DATE OF BIRTH

5KM

8KM

5 km

8km

1​

ARMIN BRANDLE

BRANDLE

ARMIN

1.44.03

1​

44​

3​

03

1.44.03

1:44:03​

0​

0​

00​

00

0.0.00

0:00:00​

2​

DENISE DARLOW

DARLOW

DENISE

44.03

0​

44​

3​

03

0.44.03

0:44:03​

0​

0​

00​

00

0.0.00

0:00:00​

3​

GEORGE WINSHIP

WINSHIP

GEORGE

44.03

0​

44​

3​

03

0.44.03

0:44:03​

0​

0​

00​

00

0.0.00

0:00:00​

4​

LOCHI LOCHNER

LOCHNER

LOCHI

44.03

0​

44​

3​

03

0.44.03

0:44:03​

0​

0​

00​

00

0.0.00

0:00:00​

5​

KAYLA NELL

NELL

KAYLA

34.12

0​

0​

0​

00

0.0.00

0:00:00​

0​

34​

12​

12

0.34.12

0:34:12​

6​

MICHAEL BURGER

BURGER

MICHAEL

25.10

0​

25​

10​

10

0.25.10

0:25:10​

0​

0​

00​

00

0.0.00

0:00:00​

7​

SHAUN ATTENBOROUGH

ATTENBOROUGH

SHAUN

42.20

0​

0​

0​

00

0.0.00

0:00:00​

0​

42​

20​

20

0.42.20

0:42:20​

8​

NEUBERT LYNNE

LYNNE

NEUBERT

42.20

0​

0​

0​

00

0.0.00

0:00:00​

0​

42​

20​

20

0.42.20

0:42:20​

9​

TONY CLARKE

CLARKE

TONY

45.18

0​

0​

0​

00

0.0.00

0:00:00​

0​

45​

18​

18

0.45.18

0:45:18​

10​

GRAHAM ESHMADE

ESHMADE

GRAHAM

32.29

0​

0​

0​

00

0.0.00

0:00:00​

0​

32​

29​

29

0.32.29

0:32:29​

Sheet 3 Nov 21.

Female Time Trial

Ranking

Points

2021-2022

03-Nov-2021

Todays date =Today()

NO

ranking

NAME & Surname

DOB

AGE

5km

8km

Points

JUNIOR FEMALE UP TO 19

2022/04/19​

1

ZENE GERBER

21 Jun 2010​

11

#N/A​

#N/A​

#N/A​

#N/A​

11

#N/A​

#N/A​

#N/A

2022/04/19​

2

RUTH SCHNETLER

19 Oct 2004​

17

0.0.00

#VALUE!​

0.32.09

00.32.09​

17

#VALUE!​

#VALUE!​

#VALUE!

The sheet that has the formulas

 

[Joe4]

OK, it appears then the values in column O are actually probably numeric with special formatting. Is that correct?

Note in the formula you have in M6, you start out with this check:

=IF(AND(H6=0,J6=0),

So, you are checking for the scenario where BOTH H6 and J6 are 0, but you are not addressing the possibiliy of H6 is zero, but J6 is NOT zero.

Your second IF in that formula in M6 starts out like this:

IF(H6="",

Maybe try changing it to:

IF(OR(H6=0,H6=""),

and see if that fixes your issue.

 

[Brentsa]

IF(OR(H6=0,H6="")

=IF(OR(H6=0,H6=""),0,IF(H6="",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24,(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24))

Is this your suggestion? if yes, then I still get a error

Maybe instead of trying to compare the two maybe i must just add a column and do two separate formula's

 

[Joe4]

=IF(OR(H6=0,H6=""),0,IF(H6="",(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24,(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24))

Is this your suggestion? if yes, then I still get a error

Maybe instead of trying to compare the two maybe i must just add a column and do two separate formula's

No, you mixed up the IFs. Note I was explicitly talking about the FIRST and SECOND IF statements.
Firstly, I was showing how the logic in your formula does not address a certain scenario.

This is what I was suggesting (note the red is the only thing changed):

=IF(AND(H6=0,J6=0),0,IF(OR(H6=0,H6=""),(LEFT(J6,(LEN(J6)-6))+(MID(J6,(LEN(J6)-4),2)/60)+(RIGHT(J6,2)/3600))/24,(LEFT(H6,(LEN(H6)-6))+(MID(H6,(LEN(H6)-4),2)/60)+(RIGHT(H6,2)/3600))/24))