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..

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..

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..

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..

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..