How to make VLOOKUP work for this

[DavidJ726]

I have a "master" spreadsheet where column A is a time column and every row
represents a second (in plain text format) such as 00:00, 00:01, 00:02....
00:59:, 01:00, 01:01, 01:02,... and continues on for 24 minutes... 23:59,
24:00. (1442 rows). Off to the side (I'm thinking of putting it on
another sheet though) I have a small table where I'll enter the time and
corresponding data I need populated in Column B, and will look something
like the following example;

My question(s) are, is the VLOOKUP function the best way to populate column
B with the values? It's a real pain to scroll down through 1400+ rows just
to enter data in 8-12 cells. And would the formula then be entered into
every cell in Column B??? I've been working with this but just can't seem
to get it to work. I was reviewing other posts here about the VLOOKUP
function and I suspect I need more help than what I'm getting from Excels
built in or the MS on-line help.

Many thanks,
David

Example;

00:00 6
00:30 4
01:30 11
02:30 10
08:45 8
12:00 6
15:15 4
19:00 2

The times and the numeric values are a profile that can change from day to
day, so the next day might look like this;

00:00 8
01:45 11
03:00 9
08:00 6
13:15 4
15:45 2

 

[Bob Phillips]

Yeah, just use it with a lookup type of TRUE

=VLOOKUP(A1,$K$1:$M$30,2,TRUE)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[DavidJ726]

Hi Bob,

Thanks for the response. It works, sort of... I had to change the
condition to FALSE so it would only return a value if it was an exact match.
Otherwise it was giving inaccurate results in all cells. Also, is there a
way to supress the #N/A error message? I'm looking at the IF ISERROR and
other search results, but not sure if that will work.

What the ideal situation would be, is if there was a way to enter the value
in the cells without having to have the formula in the cells. My goal is to
reference those values in a chart with some other data and I'm not sure how
to do that yet with the formulas and error messages. I've had to do
something like that in the past with IF statements (I think)... but I'll
cross that bridge when I get to it.

Thanks again,
David

 

[Bob Phillips]

David,

You would so that with

=IF(ISNA(VLOOKUP(A1,$K$1:$M$30,2,FALSE)),"",VLOOKUP(A1,$K$1:$M$30,2,TRUE))

but how would you find 1 sec, 2 secs etc. ? I assumed that your table had
batches of values, and they would match many to one.

The other way would need VBA code to achieve that, assuming that you mean
you will use those values in your chart.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)