Forum Discussion
#value error when using vlookup
You can achieve the desired behavior by incorporating the IFERROR function along with VLOOKUP to handle the #VALUE! errors gracefully. Here's how you can modify your formula:
=IFERROR( ((IF(P3<>"", P3/P2, 0))*H3) + ((IF(P4<>"", P4/P2, 0))*H4) + ((IF(P5<>"", P5/P2, 0))*H5), "")
This formula checks if cells P3, P4, and P5 contain values. If they do, it calculates the respective portions of the formula. If not, it uses 0 in place of the missing value. Then, IFERROR handles any remaining #VALUE! errors and replaces them with an empty string ("").
You can copy and paste this formula for your other sets of cells, and it will adapt to the presence or absence of data accordingly. This way, it will still calculate the sum with the available data and ignore the missing data.
You can achieve the desired behavior by incorporating the IFERROR function along with VLOOKUP to handle the #VALUE! errors gracefully. Here's how you can modify your formula:
=IFERROR( ((IF(P3<>"", P3/P2, 0))*H3) + ((IF(P4<>"", P4/P2, 0))*H4) + ((IF(P5<>"", P5/P2, 0))*H5), "")
This formula checks if cells P3, P4, and P5 contain values. If they do, it calculates the respective portions of the formula. If not, it uses 0 in place of the missing value. Then, IFERROR handles any remaining #VALUE! errors and replaces them with an empty string ("").
You can copy and paste this formula for your other sets of cells, and it will adapt to the presence or absence of data accordingly. This way, it will still calculate the sum with the available data and ignore the missing data.
NikolinoDE Thank you very much for this.
