Look Up

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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,"")
 
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
 
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.
 
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.
Click to expand...

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.
 
Thankyou! Thats exactly what I wanted!

Dallman Ross said:
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.
Click to expand...
 
Thank you for your help

Roger Govier said:
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.
Click to expand...
 
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
 
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
 
Hi Fiona

Try entering in D1
=IF(SUMPRODUCT(--(C1=$A$1:$A$3)),C1,"")
and copy down through D2:D100
 
Back
Top