Formula is not showing if data missing despite formula being in the cell.

[ghrek]

Hi

I have a workbook that has a formula in and im having issues with it as in its not showing

The formula is there and it appears to be missing on some cells only on the tab called "TABLEAU" and PREVREC.

Enclosed is the actual workbook im using.

Due to file size ive put the one drive link here and this is the actual workbook im using... CUBIC MACRO 2111.xlsm

sheet tableau
rows
1805
3790
3802
3866
3887
3890

sheet prev rec
rows
4911
5441
5456

Any ideas?

CUBIC MACRO 2111.xlsm

 

[StephenCrump]

Tableau!F1805 contains the formula:

=IFNA(IF(E1805=VLOOKUP(A1805,WATFORD!A:E,5,FALSE),"OK",""),"N/A")

E1805 is £30.00 The VLOOKUP() is returning 0. Therefore the IF part of your formula returns the null string "", i.e. the formula is working as it's written.

What did you want the formula to return here?

 

[ghrek]

Like all the others in the workbook. Show as OK or N/A. Just can’t understand why them few don’t do it.

 

[StephenCrump]

There are three possibilities for the VLOOKUP

- It fails - you return N/A
- The lookup value matches - you return OK
- The lookup value doesn't match, e.g. as in Tableau!F1805. What result do you want here - OK or N/A?

 

[ghrek]

N/A if possible please .

thanks

 

[StephenCrump]

Tableau!F1805: =IFNA(IF(E1805=VLOOKUP(A1805,WATFORD!A:E,5,),"OK","N/A"),"N/A")

 

[ghrek]

Many Thanks