x and x+1
 

i have two colums of data (B4:C60) that is related by row, i want to analyse
the data so that if let's say A1 is equal to any of the B4:B60, then i
return the corresponding data from the D column in A2. for example, if i
enter Blob in A1 and Blob is also in B26, then A2 will return C26. i could
do it with the IF function but i can only use it inline 7 times and no more.
if there a simple function like =IF(A1=B(x),C(x),(x=x+1)) that could work?
(except that this one doesn't, it does not recognize what i mean by the
unknown (x)
looking forward to any help...
thanks

x and x+1
 

=VLOOKUP(A1,B4:C60,2,0)

in A2



--
Regards,

Peo Sjoblom

(No private emails please)


"stephenlewin" wrote in message
...
i have two colums of data (B4:C60) that is related by row, i want to
analyse
the data so that if let's say A1 is equal to any of the B4:B60, then i
return the corresponding data from the D column in A2. for example, if i
enter Blob in A1 and Blob is also in B26, then A2 will return C26. i
could
do it with the IF function but i can only use it inline 7 times and no
more.
if there a simple function like =IF(A1=B(x),C(x),(x=x+1)) that could work?
(except that this one doesn't, it does not recognize what i mean by the
unknown (x)
looking forward to any help...
thanks

x and x+1
 

If I understand you correctly, you want to see if the value in A1 matches any
of the values in B4:B60. If YES, then return the corresponding value in Col C
to A2.

Try this:
A2: =VLOOKUP(A1,B4:C60,2,0)

Does that work?

***********
Regards,
Ron


"stephenlewin" wrote:

i have two colums of data (B4:C60) that is related by row, i want to analyse
the data so that if let's say A1 is equal to any of the B4:B60, then i
return the corresponding data from the D column in A2. for example, if i
enter Blob in A1 and Blob is also in B26, then A2 will return C26. i could
do it with the IF function but i can only use it inline 7 times and no more.
if there a simple function like =IF(A1=B(x),C(x),(x=x+1)) that could work?
(except that this one doesn't, it does not recognize what i mean by the
unknown (x)
looking forward to any help...
thanks

x and x+1
 

=IF(OR(A1=B(x),A1=C(x)),Value if True, Value if false)

by using the OR function with the IF function you can increase the
number of conditions allowed. The AND function also works as above
except all the conditions must be met instead of only one.
Hope this helps.


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=488423

x and x+1
 

excellent... it work, now there is another twist, what if i want to return
the value that is two columns away from the one the has the lookup data, for
example if A1 matches B34, i want E34 returned, not C34, how do i do it?

"Ron Coderre" wrote:

If I understand you correctly, you want to see if the value in A1 matches any
of the values in B4:B60. If YES, then return the corresponding value in Col C
to A2.

Try this:
A2: =VLOOKUP(A1,B4:C60,2,0)

Does that work?

***********
Regards,
Ron


"stephenlewin" wrote:

i have two colums of data (B4:C60) that is related by row, i want to analyse
the data so that if let's say A1 is equal to any of the B4:B60, then i
return the corresponding data from the D column in A2. for example, if i
enter Blob in A1 and Blob is also in B26, then A2 will return C26. i could
do it with the IF function but i can only use it inline 7 times and no more.
if there a simple function like =IF(A1=B(x),C(x),(x=x+1)) that could work?
(except that this one doesn't, it does not recognize what i mean by the
unknown (x)
looking forward to any help...
thanks

x and x+1
 

Here you go:
A2: =VLOOKUP(A1,B4:E60,4,0)

Does that help?

***********
Regards,
Ron


"stephenlewin" wrote:

excellent... it work, now there is another twist, what if i want to return
the value that is two columns away from the one the has the lookup data, for
example if A1 matches B34, i want E34 returned, not C34, how do i do it?

"Ron Coderre" wrote:

If I understand you correctly, you want to see if the value in A1 matches any
of the values in B4:B60. If YES, then return the corresponding value in Col C
to A2.

Try this:
A2: =VLOOKUP(A1,B4:C60,2,0)

Does that work?

***********
Regards,
Ron


"stephenlewin" wrote:

i have two colums of data (B4:C60) that is related by row, i want to analyse
the data so that if let's say A1 is equal to any of the B4:B60, then i
return the corresponding data from the D column in A2. for example, if i
enter Blob in A1 and Blob is also in B26, then A2 will return C26. i could
do it with the IF function but i can only use it inline 7 times and no more.
if there a simple function like =IF(A1=B(x),C(x),(x=x+1)) that could work?
(except that this one doesn't, it does not recognize what i mean by the
unknown (x)
looking forward to any help...
thanks

x and x+1
 

Change the 2 in the formula to 4

--
Regards,

Peo Sjoblom

(No private emails please)


"stephenlewin" wrote in message
...
excellent... it work, now there is another twist, what if i want to return
the value that is two columns away from the one the has the lookup data,
for
example if A1 matches B34, i want E34 returned, not C34, how do i do it?

"Ron Coderre" wrote:

If I understand you correctly, you want to see if the value in A1 matches
any
of the values in B4:B60. If YES, then return the corresponding value in
Col C
to A2.

Try this:
A2: =VLOOKUP(A1,B4:C60,2,0)

Does that work?

***********
Regards,
Ron


"stephenlewin" wrote:

i have two colums of data (B4:C60) that is related by row, i want to
analyse
the data so that if let's say A1 is equal to any of the B4:B60, then i
return the corresponding data from the D column in A2. for example, if
i
enter Blob in A1 and Blob is also in B26, then A2 will return C26. i
could
do it with the IF function but i can only use it inline 7 times and no
more.
if there a simple function like =IF(A1=B(x),C(x),(x=x+1)) that could
work?
(except that this one doesn't, it does not recognize what i mean by the
unknown (x)
looking forward to any help...
thanks

x and x+1
 

ahhh, i'm starting to understand, thank you Ron.

Steve

"Ron Coderre" wrote:

Here you go:
A2: =VLOOKUP(A1,B4:E60,4,0)

Does that help?

***********
Regards,
Ron


"stephenlewin" wrote:

excellent... it work, now there is another twist, what if i want to return
the value that is two columns away from the one the has the lookup data, for
example if A1 matches B34, i want E34 returned, not C34, how do i do it?

"Ron Coderre" wrote:

If I understand you correctly, you want to see if the value in A1 matches any
of the values in B4:B60. If YES, then return the corresponding value in Col C
to A2.

Try this:
A2: =VLOOKUP(A1,B4:C60,2,0)

Does that work?

***********
Regards,
Ron


"stephenlewin" wrote:

i have two colums of data (B4:C60) that is related by row, i want to analyse
the data so that if let's say A1 is equal to any of the B4:B60, then i
return the corresponding data from the D column in A2. for example, if i
enter Blob in A1 and Blob is also in B26, then A2 will return C26. i could
do it with the IF function but i can only use it inline 7 times and no more.
if there a simple function like =IF(A1=B(x),C(x),(x=x+1)) that could work?
(except that this one doesn't, it does not recognize what i mean by the
unknown (x)
looking forward to any help...
thanks