Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Hi, I am trying to get a lookup formula to read 3 cells in one row, then
compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. Thank you in advance Fiona |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Hi Fiona
Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") -- Regards Roger Govier "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. Thank you in advance Fiona |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Thanks Roger, sorry if I was a bit unclear, the formula I want to work is
=LOOKUP(A1:A3,C1:C3,D1:D3) As i want the formula to check 3 cells instead of 1 "Roger Govier" wrote: Hi Fiona Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") -- Regards Roger Govier "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. Thank you in advance Fiona |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Hi Fiona
Lookup cannot work in this way. Can you just describe exactly what you want to achieve e.g. If the value in C1 is found in column A or in Column B, then place the value from C2 in column D. -- Regards Roger Govier "Fiona" wrote in message ... Thanks Roger, sorry if I was a bit unclear, the formula I want to work is =LOOKUP(A1:A3,C1:C3,D1:D3) As i want the formula to check 3 cells instead of 1 "Roger Govier" wrote: Hi Fiona Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") -- Regards Roger Govier "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. Thank you in advance Fiona |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
"Fiona" wrote in message
... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. I think this might do what you want: =IF(ISERROR(MATCH(C1,A1:A3,FALSE)),"",C2) I could not see your posts in the group, but only could see Roger's followups for some reason. From what you describe, though, the above ought to work, I believe. One possible flaw is that it is case-insensitive. If you want case- sensitive matching, we'll have to think of something else. -- dman |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Thankyou! Thats exactly what I wanted!
"Dallman Ross" wrote: "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. I think this might do what you want: =IF(ISERROR(MATCH(C1,A1:A3,FALSE)),"",C2) I could not see your posts in the group, but only could see Roger's followups for some reason. From what you describe, though, the above ought to work, I believe. One possible flaw is that it is case-insensitive. If you want case- sensitive matching, we'll have to think of something else. -- dman |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Thank you for your help
"Roger Govier" wrote: Hi Fiona Lookup cannot work in this way. Can you just describe exactly what you want to achieve e.g. If the value in C1 is found in column A or in Column B, then place the value from C2 in column D. -- Regards Roger Govier "Fiona" wrote in message ... Thanks Roger, sorry if I was a bit unclear, the formula I want to work is =LOOKUP(A1:A3,C1:C3,D1:D3) As i want the formula to check 3 cells instead of 1 "Roger Govier" wrote: Hi Fiona Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") -- Regards Roger Govier "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. Thank you in advance Fiona |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Hi Roger, I want a formula, say in D3, to equal C2 if any of the cells A1, A2
or A3 contain C1 Thank you "Roger Govier" wrote: Hi Fiona Lookup cannot work in this way. Can you just describe exactly what you want to achieve e.g. If the value in C1 is found in column A or in Column B, then place the value from C2 in column D. -- Regards Roger Govier "Fiona" wrote in message ... Thanks Roger, sorry if I was a bit unclear, the formula I want to work is =LOOKUP(A1:A3,C1:C3,D1:D3) As i want the formula to check 3 cells instead of 1 "Roger Govier" wrote: Hi Fiona Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") -- Regards Roger Govier "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. Thank you in advance Fiona |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
=IF(OR(A1:A3=C1),C2,"") entered as an array formula (Control Shift Enter).
-- David Biddulph "Fiona" wrote in message ... Hi Roger, I want a formula, say in D3, to equal C2 if any of the cells A1, A2 or A3 contain C1 "Roger Govier" wrote: Hi Fiona Lookup cannot work in this way. Can you just describe exactly what you want to achieve e.g. If the value in C1 is found in column A or in Column B, then place the value from C2 in column D. "Fiona" wrote in message ... Thanks Roger, sorry if I was a bit unclear, the formula I want to work is =LOOKUP(A1:A3,C1:C3,D1:D3) As i want the formula to check 3 cells instead of 1 "Roger Govier" wrote: Hi Fiona Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Thanks David,
Is there any way we can move this on to incorporate a lookup formula, so it would equal Lookup for example, If A1:A3 contains any of the cells in C1:C100 equal the corresponding code in D1:D100. Thank you "David Biddulph" wrote: =IF(OR(A1:A3=C1),C2,"") entered as an array formula (Control Shift Enter). -- David Biddulph "Fiona" wrote in message ... Hi Roger, I want a formula, say in D3, to equal C2 if any of the cells A1, A2 or A3 contain C1 "Roger Govier" wrote: Hi Fiona Lookup cannot work in this way. Can you just describe exactly what you want to achieve e.g. If the value in C1 is found in column A or in Column B, then place the value from C2 in column D. "Fiona" wrote in message ... Thanks Roger, sorry if I was a bit unclear, the formula I want to work is =LOOKUP(A1:A3,C1:C3,D1:D3) As i want the formula to check 3 cells instead of 1 "Roger Govier" wrote: Hi Fiona Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up
Hi Fiona
Try entering in D1 =IF(SUMPRODUCT(--(C1=$A$1:$A$3)),C1,"") and copy down through D2:D100 -- Regards Roger Govier "Fiona" wrote in message ... Thanks David, Is there any way we can move this on to incorporate a lookup formula, so it would equal Lookup for example, If A1:A3 contains any of the cells in C1:C100 equal the corresponding code in D1:D100. Thank you "David Biddulph" wrote: =IF(OR(A1:A3=C1),C2,"") entered as an array formula (Control Shift Enter). -- David Biddulph "Fiona" wrote in message ... Hi Roger, I want a formula, say in D3, to equal C2 if any of the cells A1, A2 or A3 contain C1 "Roger Govier" wrote: Hi Fiona Lookup cannot work in this way. Can you just describe exactly what you want to achieve e.g. If the value in C1 is found in column A or in Column B, then place the value from C2 in column D. "Fiona" wrote in message ... Thanks Roger, sorry if I was a bit unclear, the formula I want to work is =LOOKUP(A1:A3,C1:C3,D1:D3) As i want the formula to check 3 cells instead of 1 "Roger Govier" wrote: Hi Fiona Not sure I fully understand your question, but does the following do what you want. =IF(ISNUMBER(SEARCH(C1,A1:A3&B1:B3)),C2,"") "Fiona" wrote in message ... Hi, I am trying to get a lookup formula to read 3 cells in one row, then compare it to my look up list. e.g, A1 is South A2 is East, A3 is London, B1 is London, B2 is England, B3 is UK In C1 I have London and C2 I have LOND. I want my formula to equal C2 if any of the cells A1 - A3 equal C1, and so on. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|