Forum Discussion
Formula help ISNA MATCH VLOOKUP
Hi. I'm on a PC (Win10) using Excel for 365 MSO ( 16.0.13426.20524, 64-bit)
This formula in 2019 worked to add zeroes to empty spots in my data. Now it does not. What gives?
=IF(ISNA(MATCH(D3,$A$3:$A$355,0)),””,VLOOKUP(D3,$A$3:$B$355,2,FALSE))
I have a data set with two columns:
(A) Time
(B) #_of_events
I need to create 2 new columns:
(D) lists all possible time values from 0.0 to 0.07585, separated by time steps of 0.000074 sec,
(E) takes every value from "#_of_Events" (in B) and places each into line with its corresponding time value (now in (D). For any time value in D when no events were observed, a 'zero' will be placed in E, meaning no events occurred at this time point.
That's the famous floating point error.
Change the formula in column to:
=ROUND(D2+0.000074,6)
4 Replies
=XLOOKUP(D2,$A$2:$A$355,$B$2:$B$355,0)Thanks for the response.
Your formula was successful for some rows, but did not transfer column B values for many other rows, inserting zeroes where there should be nonzero values (see attached). Any ideas?
That's the famous floating point error.
Change the formula in column to:
=ROUND(D2+0.000074,6)
