Forum Discussion
Solved
#value error when using vlookup
Hi
So I'm trying to get a formula in a cell, but, as this same formula will be used for multiple cells, I need to extend the formula. For instance I have this formula =((P3/P2)*H3)+((P4/P2)*H4)+((P5/P2)*H5). There are data in P3&H3 and P4&H4 but not in P5&H5. So makes sense that it comes up with the #value error. However, for the next set of cells, which will be =((P10/P19)*H10)+((P11/P9)*H11)+((P12/P9)*H12), there will be data on P12&H12, so there will be no #value error. As I will have to copy and paste this formula on about 100 set of lines, it will take a while to get each section with its own set of formula range as the formula i will have to use, will be based on 10 rows of data. Some will have 2 row of data and some 10.
IFERROR will only zero out the result. All I want is that even if there is no data in specific rows, it will still calculate the sum with what has been populated. Basically, ignore missing data and provide result on what is available.
Let me know please
Thanks
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.
