|
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
finding a value between a range
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 |
| All times are GMT +1. The time now is 08:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com