Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet 1 "hr"

Any suggestion

Thanks

Susan

No Hr values match in your example! Create an example with some matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet 1 "hr"

Any suggestion

Thanks

Susan

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point - basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr etc.

What is the difference between using "true" and "false" in the vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet 1 "hr"

Any suggestion

Thanks

Susan

Hi Susan,

The formula is
=VLOOKUP(A2,Sheet2!A:B,2)

but in order to get good results you must have a first row in sheet2 with
an Hr value 0 and sheet2 must be sorted in ascending order by column A.

FALSE in VLOOKUP searches for an exact match, TRUE or omitted searches for
the next closest match, see VLOOKUP Help!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point - basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr etc.

What is the difference between using "true" and "false" in the vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet 1 "hr"

Any suggestion

Thanks

Susan

Hello Stefi,

Thanks - but this doesn't work. The formula returns a #N/A up to 21 hr and
then gives and "action" for each hr after that i.e.,

20.85 3.5 #N/A
20.9 3.5 #N/A
20.95 3.5 #N/A
21 3.5 O2 Trap changed
21.05 3.5 O2 Trap changed
21.1 3.5 O2 Trap changed
21.15 3.5 O2 Trap changed
21.2 3.5 O2 Trap changed
21.25 3.5 O2 Trap changed
21.3 3.5 O2 Trap changed
21.35 3.5 O2 Trap changed

I need it to return the action of ONLY the Hr that most closely matches the
Hr in the second sheet 2

Thanks

Susan

"Stefi" wrote:

Hi Susan,

The formula is
=VLOOKUP(A2,Sheet2!A:B,2)

but in order to get good results you must have a first row in sheet2 with
an Hr value 0 and sheet2 must be sorted in ascending order by column A.

FALSE in VLOOKUP searches for an exact match, TRUE or omitted searches for
the next closest match, see VLOOKUP Help!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point - basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr etc.

What is the difference between using "true" and "false" in the vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet 1 "hr"

Any suggestion

Thanks

Susan

Change the second table to

Hr Action
0 O2 Trap changed
21 O2 Trap changed
44 Computer failure
69 O2 Trap changed
92 Nutrients added
116 O2 Trap changed


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Susan" wrote in message
...
Hello Stefi,

Thanks - but this doesn't work. The formula returns a #N/A up to 21 hr and
then gives and "action" for each hr after that i.e.,

20.85 3.5 #N/A
20.9 3.5 #N/A
20.95 3.5 #N/A
21 3.5 O2 Trap changed
21.05 3.5 O2 Trap changed
21.1 3.5 O2 Trap changed
21.15 3.5 O2 Trap changed
21.2 3.5 O2 Trap changed
21.25 3.5 O2 Trap changed
21.3 3.5 O2 Trap changed
21.35 3.5 O2 Trap changed

I need it to return the action of ONLY the Hr that most closely matches
the
Hr in the second sheet 2

Thanks

Susan

"Stefi" wrote:

Hi Susan,

The formula is
=VLOOKUP(A2,Sheet2!A:B,2)

but in order to get good results you must have a first row in sheet2
with
an Hr value 0 and sheet2 must be sorted in ascending order by column A.

FALSE in VLOOKUP searches for an exact match, TRUE or omitted searches
for
the next closest match, see VLOOKUP Help!

Regards,
Stefi

"Susan" ezt írta:

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point -
basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr
etc.

What is the difference between using "true" and "false" in the vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some
matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

"Susan" ezt írta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and
intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet
1 "hr"

Any suggestion

Thanks

Susan

Bob,
I think it's a bit more complex than your suggested change: what
is required is the first hit closest to 21 and then to ignore all hits
(blank) until 44 or nearest to this.

I haven't an answer - way beyond my abilities with formulae; but probably
could manage a VBA solution(?).

"Bob Phillips" wrote:

Change the second table to

Hr Action
0 O2 Trap changed
21 O2 Trap changed
44 Computer failure
69 O2 Trap changed
92 Nutrients added
116 O2 Trap changed


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Susan" wrote in message
...
Hello Stefi,

Thanks - but this doesn't work. The formula returns a #N/A up to 21 hr and
then gives and "action" for each hr after that i.e.,

20.85 3.5 #N/A
20.9 3.5 #N/A
20.95 3.5 #N/A
21 3.5 O2 Trap changed
21.05 3.5 O2 Trap changed
21.1 3.5 O2 Trap changed
21.15 3.5 O2 Trap changed
21.2 3.5 O2 Trap changed
21.25 3.5 O2 Trap changed
21.3 3.5 O2 Trap changed
21.35 3.5 O2 Trap changed

I need it to return the action of ONLY the Hr that most closely matches
the
Hr in the second sheet 2

Thanks

Susan

"Stefi" wrote:

Hi Susan,

The formula is
=VLOOKUP(A2,Sheet2!A:B,2)

but in order to get good results you must have a first row in sheet2
with
an Hr value 0 and sheet2 must be sorted in ascending order by column A.

FALSE in VLOOKUP searches for an exact match, TRUE or omitted searches
for
the next closest match, see VLOOKUP Help!

Regards,
Stefi

"Susan" ezt Ã*rta:

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point -
basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr
etc.

What is the difference between using "true" and "false" in the vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some
matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

"Susan" ezt Ã*rta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and
intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet
1 "hr"

Any suggestion

Thanks

Susan

I agree, but at one point the OP said ... I would be happy to "hit" either
the 20.99 or the 21.04 point ..., so I took a liberty <g

Bob

"Toppers" wrote in message
...
Bob,
I think it's a bit more complex than your suggested change:
what
is required is the first hit closest to 21 and then to ignore all hits
(blank) until 44 or nearest to this.

I haven't an answer - way beyond my abilities with formulae; but probably
could manage a VBA solution(?).

"Bob Phillips" wrote:

Change the second table to

Hr Action
0 O2 Trap changed
21 O2 Trap changed
44 Computer failure
69 O2 Trap changed
92 Nutrients added
116 O2 Trap changed


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Susan" wrote in message
...
Hello Stefi,

Thanks - but this doesn't work. The formula returns a #N/A up to 21 hr
and
then gives and "action" for each hr after that i.e.,

20.85 3.5 #N/A
20.9 3.5 #N/A
20.95 3.5 #N/A
21 3.5 O2 Trap changed
21.05 3.5 O2 Trap changed
21.1 3.5 O2 Trap changed
21.15 3.5 O2 Trap changed
21.2 3.5 O2 Trap changed
21.25 3.5 O2 Trap changed
21.3 3.5 O2 Trap changed
21.35 3.5 O2 Trap changed

I need it to return the action of ONLY the Hr that most closely matches
the
Hr in the second sheet 2

Thanks

Susan

"Stefi" wrote:

Hi Susan,

The formula is
=VLOOKUP(A2,Sheet2!A:B,2)

but in order to get good results you must have a first row in sheet2
with
an Hr value 0 and sheet2 must be sorted in ascending order by column
A.

FALSE in VLOOKUP searches for an exact match, TRUE or omitted searches
for
the next closest match, see VLOOKUP Help!

Regards,
Stefi

"Susan" ezt írta:

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There
is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point -
basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr
etc.

What is the difference between using "true" and "false" in the
vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some
matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

"Susan" ezt írta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and
intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in
sheet
1 "hr"

Any suggestion

Thanks

Susan

Try this (and add blank line - row 2 - at beginning of your data:

=IF(MATCH(A2,Sheet2!$A$2:$A$8,1)=MATCH(A3,Sheet2!$ A$2:$A$8,1),"",IF(ISNA(VLOOKUP(A3,Sheet2!A:B,2))," ",VLOOKUP(A3,Sheet2!A:B,2)))


HTH

"Susan" wrote:

Hello Stefi,

Thanks - but this doesn't work. The formula returns a #N/A up to 21 hr and
then gives and "action" for each hr after that i.e.,

20.85 3.5 #N/A
20.9 3.5 #N/A
20.95 3.5 #N/A
21 3.5 O2 Trap changed
21.05 3.5 O2 Trap changed
21.1 3.5 O2 Trap changed
21.15 3.5 O2 Trap changed
21.2 3.5 O2 Trap changed
21.25 3.5 O2 Trap changed
21.3 3.5 O2 Trap changed
21.35 3.5 O2 Trap changed

I need it to return the action of ONLY the Hr that most closely matches the
Hr in the second sheet 2

Thanks

Susan

"Stefi" wrote:

Hi Susan,

The formula is
=VLOOKUP(A2,Sheet2!A:B,2)

but in order to get good results you must have a first row in sheet2 with
an Hr value 0 and sheet2 must be sorted in ascending order by column A.

FALSE in VLOOKUP searches for an exact match, TRUE or omitted searches for
the next closest match, see VLOOKUP Help!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point - basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr etc.

What is the difference between using "true" and "false" in the vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet 1 "hr"

Any suggestion

Thanks

Susan

Thanks - that worked a treat. Very happy.

Why do you need the first line of sheet 2 blank? I found if I didn't have
this first line, it didn't pick up the first "action" at 21 hr and gave a N/A
but for every line afterwards work as expected?

Thanks again

Susan

"Toppers" wrote:

Try this (and add blank line - row 2 - at beginning of your data:

=IF(MATCH(A2,Sheet2!$A$2:$A$8,1)=MATCH(A3,Sheet2!$ A$2:$A$8,1),"",IF(ISNA(VLOOKUP(A3,Sheet2!A:B,2))," ",VLOOKUP(A3,Sheet2!A:B,2)))


HTH

"Susan" wrote:

Hello Stefi,

Thanks - but this doesn't work. The formula returns a #N/A up to 21 hr and
then gives and "action" for each hr after that i.e.,

20.85 3.5 #N/A
20.9 3.5 #N/A
20.95 3.5 #N/A
21 3.5 O2 Trap changed
21.05 3.5 O2 Trap changed
21.1 3.5 O2 Trap changed
21.15 3.5 O2 Trap changed
21.2 3.5 O2 Trap changed
21.25 3.5 O2 Trap changed
21.3 3.5 O2 Trap changed
21.35 3.5 O2 Trap changed

I need it to return the action of ONLY the Hr that most closely matches the
Hr in the second sheet 2

Thanks

Susan

"Stefi" wrote:

Hi Susan,

The formula is
=VLOOKUP(A2,Sheet2!A:B,2)

but in order to get good results you must have a first row in sheet2 with
an Hr value 0 and sheet2 must be sorted in ascending order by column A.

FALSE in VLOOKUP searches for an exact match, TRUE or omitted searches for
the next closest match, see VLOOKUP Help!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hey Stefi,

Sheet 1 continues in a semi-continuous way up to ~1500 Hr's. There is ~
40000 data points (rows of data)

The data "around" the first point to match is....

Hr mL Gas
20.94 3.5
20.99 3.5
21.04 3.5
21.09 3.5
21.14 3.5
21.19 3.5
21.24 3.5

I would be happy to "hit" either the 20.99 or the 21.04 point - basically I
want to find the "closest" point to 21 hrs, then 44 hrs, then 69 hr etc.

What is the difference between using "true" and "false" in the vlookup
function?

CHeers

Susan

"Stefi" wrote:

No Hr values match in your example! Create an example with some matching
values and try
=VLOOKUP(A6,Sheet2!A:B,2,TRUE)
and see what happens!

Regards,
Stefi

€žSusan€� ezt Ã*rta:

Hello,

I have a sheet that has data such as:

Hr mL gas
0 0
0.05 3.5
0.1 3.5
0.15 3.5
0.19 3.5
0.25 3.5

And another sheet that looks like this:

Hr Action
21 O2 Trap changed
44 O2 Trap changed
69 Computer failure
92 O2 Trap changed
116 Nutrients added
164 O2 Trap changed

I want to add a column to the first sheet called "action" and intergrate the
two tables.

I tried : VLOOKUP(A6,Sheet2!A:B,2,FALSE)...in sheet 1 column C

Problem is that "Hr" in sheet 2 does not necessiarly occur in sheet 1 "hr"

Any suggestion

Thanks

Susan