Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup when value is between 2 columns
Hi,
I need to lookup a column if the value is between the first 2 columns Col 1 Col2 Col3 Col 4 1 5 Yes 10 6 10 Yes 20 11 15 No 30 If the value I am looking up is 7, I need to return Yes from Col 3. So if value is between col 1 and col 2, return Col 3. I was able to get Col 4 using Sum (if..) but do not know how to return the text in Col 3 Thanks in advance.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup when value is between 2 columns
If column 1 is in E2 and Col 2 is F2 Col 3 =
=IF(OR(E2<=$G$1,F2<=$G$1),"Yes","No") For column 4 I used =(INT(MAX(E2:F2)/$G$1)+1)*10 G1 is the number to divide by. HTH Peter "Yegod" wrote: Hi, I need to lookup a column if the value is between the first 2 columns Col 1 Col2 Col3 Col 4 1 5 Yes 10 6 10 Yes 20 11 15 No 30 If the value I am looking up is 7, I need to return Yes from Col 3. So if value is between col 1 and col 2, return Col 3. I was able to get Col 4 using Sum (if..) but do not know how to return the text in Col 3 Thanks in advance.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup when value is between 2 columns
See if these work for you.
=VLOOKUP(F9,B9:C11,2,1) Where F9 is the lookup value. B9:B11 = 1, 5, 10. C9:C11 = Yes, Yes, No. OR =LOOKUP(F9,{1,5,10},{"Yes","Yes","No"}) Whe F9 = 1 to 4 returns Yes. F9 = 5 to 9 returns Yes. F9 = 9 or returns No. HTH Regards, Howard "Yegod" wrote in message ... Hi, I need to lookup a column if the value is between the first 2 columns Col 1 Col2 Col3 Col 4 1 5 Yes 10 6 10 Yes 20 11 15 No 30 If the value I am looking up is 7, I need to return Yes from Col 3. So if value is between col 1 and col 2, return Col 3. I was able to get Col 4 using Sum (if..) but do not know how to return the text in Col 3 Thanks in advance.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup when value is between 2 columns
Try
Here 7 is the lookup value =LOOKUP(7,A1:A10,C1:C10) -- Jacob (MVP - Excel) "Yegod" wrote: Hi, I need to lookup a column if the value is between the first 2 columns Col 1 Col2 Col3 Col 4 1 5 Yes 10 6 10 Yes 20 11 15 No 30 If the value I am looking up is 7, I need to return Yes from Col 3. So if value is between col 1 and col 2, return Col 3. I was able to get Col 4 using Sum (if..) but do not know how to return the text in Col 3 Thanks in advance.. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup when value is between 2 columns
Thank you all, I used lookup.
"Jacob Skaria" wrote: Try Here 7 is the lookup value =LOOKUP(7,A1:A10,C1:C10) -- Jacob (MVP - Excel) "Yegod" wrote: Hi, I need to lookup a column if the value is between the first 2 columns Col 1 Col2 Col3 Col 4 1 5 Yes 10 6 10 Yes 20 11 15 No 30 If the value I am looking up is 7, I need to return Yes from Col 3. So if value is between col 1 and col 2, return Col 3. I was able to get Col 4 using Sum (if..) but do not know how to return the text in Col 3 Thanks in advance.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
Lookup across columns | Excel Worksheet Functions | |||
Lookup two different columns | Excel Worksheet Functions | |||
Lookup Two Columns | Excel Worksheet Functions | |||
Lookup a value using two columns | Excel Worksheet Functions |