finding a value between a range

[Guest]

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

 

[Guest]

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:

 

[Guest]

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

 

[Guest]

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:

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

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