How to make VLOOKUP work for this

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

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)

"DavidJ726" <[email protected]> wrote in message
news:#[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>