SOLVED

Formula help ISNA MATCH VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-2643079%22%20slang%3D%22en-US%22%3EFormula%20help%20ISNA%20MATCH%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2643079%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%26nbsp%3B%20I'm%20on%20a%20PC%20(Win10)%20using%20Excel%20for%20365%20MSO%20(%2016.0.13426.20524%2C%2064-bit)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20in%202019%20worked%20to%20add%20zeroes%20to%20empty%20spots%20in%20my%20data.%26nbsp%3B%20Now%20it%20does%20not.%20What%20gives%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISNA(MATCH(D3%2C%24A%243%3A%24A%24355%2C0))%2C%E2%80%9D%E2%80%9D%2CVLOOKUP(D3%2C%24A%243%3A%24B%24355%2C2%2CFALSE))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20data%20set%20with%20two%20columns%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B(A)%20Time%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B(B)%20%23_of_events%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20create%202%20new%20columns%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B(D)%20lists%20all%20possible%20time%20values%20from%200.0%20to%200.07585%2C%20separated%20by%20time%20steps%20of%200.000074%20sec%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B(E)%20takes%20every%20value%20from%20%22%23_of_Events%22%20(in%20B)%20and%20places%20each%20into%20line%20with%20its%20corresponding%20time%20value%20(now%20in%20(D).%26nbsp%3B%20For%20any%20time%20value%20in%20D%20when%20no%20events%20were%20observed%2C%20a%20'zero'%20will%20be%20placed%20in%20E%2C%20meaning%20no%20events%20occurred%20at%20this%20time%20point.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2643079%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2643157%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20ISNA%20MATCH%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2643157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127304%22%20target%3D%22_blank%22%3E%40Neuro_Bob_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXLOOKUP(D2%2C%24A%242%3A%24A%24355%2C%24B%242%3A%24B%24355%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2645544%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20ISNA%20MATCH%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2645544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3C%2FP%3E%3CP%3EThanks%20for%20the%20response.%3C%2FP%3E%3CP%3EYour%20formula%20was%20successful%20for%20some%20rows%2C%20but%20did%20not%20transfer%20column%20B%20values%20for%20many%20other%20rows%2C%20inserting%20zeroes%20where%20there%20should%20be%20nonzero%20values%20(see%20attached).%26nbsp%3B%20Any%20ideas%3F%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2645687%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20ISNA%20MATCH%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2645687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127304%22%20target%3D%22_blank%22%3E%40Neuro_Bob_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20the%20famous%20floating%20point%20error.%3C%2FP%3E%3CP%3EChange%20the%20formula%20in%20column%20to%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DROUND(D2%2B0.000074%2C6)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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_ (New 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