CODEWITHSUNDEEP
excelvlookup
yusica
yusica

Reputation: 255

Excel Vlookup returns NA

I use macro to record a live data every 5 second, and also a table with minute time column and corresponding values for lookup. I used regular vlookup with 4th argument as True, but only getting #N/A. Any tips to how to lookup with time?

=VLOOKUP(B8, Data!$A$1:$B$599, 2, TRUE)

B8 is the time in Time column in first table, Data!$A$1:$B$599 is the lookup table

Time        Data to Match
07:41:50   
07:42:00    
07:42:18    
07:42:57    
07:43:07        
07:43:17

Lookup table:

07:39:00    0.000373257
07:40:00    0.000490657
07:41:00    0.000283575
07:42:00    0.000318266
07:43:00    0.000409149
07:44:00    0.000311742
07:45:00    0.000331792
07:46:00    0.000343248
07:47:00    0.000382561

I just want to fill in the Data to Match column with corresponding values in the lookup table, rounding up or down are both fine, approximate match.

Upvotes: 0

Views: 147

Answers (1)

Martin Router King
Martin Router King

Reputation: 155

For these kind of cases I prefer using INDEX/MATCH.

=INDEX($B$1:$B$9;MATCH($E1;$A$1:$A$9;1))

Where:

B1:B9 = Range of the data you want to return

E1 = Value to compare with the index

A1:A9 = List of times where you want to match a result

The last "1" argument is to find the nearest lowest result, the list will have to be in ASCENDANT way for this.

Upvotes: 1

Related Questions