I have a VLOOKUP that looks like the following in Cells B2:B51 and C2:C51 in a sheet called Paid DD and the cells are formatted as General and Date:-

VBA Code:

```
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE)
```

DD Members is another sheet in the same workbook and the cells are formatted as General and Date in the sheet.

When a match is found, it bring the cell value as expected. However if the cell is empty in sheet DD Members, the result of the first VLOOKUP is 0 (in column B) and 00/01/00 in Column C.

Is it possible if the value to be returned by the VLOOKUP is empty, in Column B (sheet DD Members), then the VLOOKUP puts a blank in the Column B for the first VLOOKUP and the same for Column C.

The reason for the above is I have a COUNTA formula in sheet Paid DD, cell B52 (=COUNTA(B2:B51)) and C52 (=COUNTA(C2:C51)) which is returning the wrong count as the cells are not blank as they have a formula in them.

I have search the forum and even tried to

VBA Code:

`=T(VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE))`

2020 Edited Membership.xlsm | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | L | |||

1 | Pay Type | Membership Number | DO NOT DELETE, REQUIRED in SHEET PAID DD | Payment Statement number | Payment Received Date | Surname | First Name | Address | Town | Post Code | Member Type | Junior Parent | ||

2 | 99 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||

3 | SM | 00001 | SM00001 | 305 | 01/04/20 | |||||||||

4 | SM | 00002 | SM00002 | 305 | 01/04/20 | |||||||||

5 | SM | 00003 | SM00003 | 249 | 01/04/20 | |||||||||

6 | SM | 00004 | SM00004 | 303 | 01/04/20 | |||||||||

7 | SM | 00005 | SM00005 | 304 | 01/04/20 | |||||||||

8 | SM | 00006 | SM00006 | 303 | 01/04/20 | |||||||||

9 | SM | 00007 | SM00007 | 246 | 01/04/20 | |||||||||

10 | SM | 00008 | SM00008 | 303 | 01/04/20 | |||||||||

11 | SM | 00009 | SM00009 | 307 | 01/04/20 | |||||||||

12 | SM | 00010 | SM00010 | 246 | 01/04/20 | |||||||||

13 | SM | 00011 | SM00011 | 303 | 01/04/20 | |||||||||

14 | SM | 00012 | SM00012 | 307 | 01/04/20 | |||||||||

15 | SM | 00013 | SM00013 | 307 | 01/04/20 | |||||||||

16 | SM | 00014 | SM00014 | 306 | 01/04/20 | |||||||||

17 | SM | 00015 | SM00015 | 304 | 01/04/20 | |||||||||

18 | SM | 00016 | SM00016 | 307 | 01/04/20 | |||||||||

19 | SM | 00017 | SM00017 | |||||||||||

20 | SM | 00018 | SM00018 | |||||||||||

21 | SM | 00019 | SM00019 | 323 | 01/04/20 | |||||||||

22 | SM | 00020 | SM00020 | 304 | 01/04/20 | |||||||||

23 | SM | 00021 | SM00021 | 304 | 01/04/20 | |||||||||

24 | SM | 00022 | SM00022 | 306 | 01/04/20 | |||||||||

25 | SM | 00023 | SM00023 | 305 | 01/04/20 | |||||||||

26 | SM | 00024 | SM00024 | |||||||||||

27 | SM | 00025 | SM00025 | 250 | 01/04/20 | |||||||||

28 | SM | 00026 | SM00026 | 250 | 01/04/20 | |||||||||

29 | SM | 00027 | SM00027 | 305 | 01/04/20 | |||||||||

30 | SM | 00028 | SM00028 | 248 | 01/04/20 | |||||||||

31 | SM | 00029 | SM00029 | 306 | 01/04/20 | |||||||||

32 | SM | 00030 | SM00030 | 306 | 01/04/20 | |||||||||

33 | SM | 00031 | SM00031 | 304 | 01/04/20 | |||||||||

34 | SM | 00032 | SM00032 | 304 | 01/04/20 | |||||||||

35 | SM | 00033 | SM00033 | 250 | 01/04/20 | |||||||||

36 | SM | 00034 | SM00034 | 303 | 01/04/20 | |||||||||

37 | SM | 00035 | SM00035 | 304 | 01/04/20 | |||||||||

38 | SM | 00036 | SM00036 | 306 | 01/04/20 | |||||||||

39 | SM | 00037 | SM00037 | 306 | 01/04/20 | |||||||||

40 | SM | 00038 | SM00038 | 304 | 01/04/20 | |||||||||

41 | SM | 00039 | SM00039 | 304 | 01/04/20 | |||||||||

42 | SM | 00040 | SM00040 | 304 | 01/04/20 | |||||||||

DD Members |

Cell Formulas | ||
---|---|---|

Range | Formula | |

D2,F2:L2 | D2 | =COUNTA(D3:D152) |

C3:C42 | C3 | =CONCATENATE(A3,B3) |

Cells with Conditional Formatting | ||||
---|---|---|---|---|

Cell | Condition | Cell Format | Stop If True | |

A:A | Cell Value | ="RM" | text | YES |

Cell Formulas | ||
---|---|---|

Range | Formula | |

K2:K51,H2:H51,E2:E51,B2:B51 | B2 | =VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE) |

L2:L51,I2:I51,F2:F51,C2:C51 | C2 | =VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE) |

J53,H53,E53,B53:C53 | B53 | =COUNTA(B2:B51) |

B55 | B55 | =B53+E53+H53 |

I have had to delete data in sheet DD Members from Columns F onwards as it is P+C.

Any assistance offered will be appreciated.