SOLVED

Formula help ISNA MATCH VLOOKUP

Copper Contributor

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. 

 

 

4 Replies

@Neuro_Bob_ 

=XLOOKUP(D2,$A$2:$A$355,$B$2:$B$355,0)

@Detlef Lewin

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?    

best response confirmed by Neuro_Bob_ (Copper Contributor)
Solution

@Neuro_Bob_ 

That's the famous floating point error.

Change the formula in column to:

=ROUND(D2+0.000074,6)

 

Solved! Thank you kindly
1 best response

Accepted Solutions
best response confirmed by Neuro_Bob_ (Copper Contributor)
Solution

@Neuro_Bob_ 

That's the famous floating point error.

Change the formula in column to:

=ROUND(D2+0.000074,6)

 

View solution in original post