Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|