Lookup/match formula?

[Guest]

Hello, I am trying to get a formula to read the following...I have come close
to getting the answer I need, but am not quite there yet! Any help would be
much appreciated

Coloumn A contains various locations in the UK, written like - England,
South East, London, Hammersmith. Then the next line contains - England,
South, South West, Devon.

There is no consistancy in the number of words that are used, but I have a
list of UK towns in Coloumn C with a corresponding town reference in coloumn
D, for example London in C, then 123 in D.

I am trying to enter a formula in a new coloumn that shows - if coloumn A1
contains any on the UK towns listed in coloumn C (there are about 50 of them)
then show the number that is in coloumn D next to that town in my new
coloumn. I then want to copy this formula down so it reads, A2, A3 and so on.

Thank you in advance!

 

[Guest]

One way ..

Put in say, B1, and array-enter the formula, ie press CTRL+SHIFT+ENTER:
=IF(A1="","",INDEX(D$1$50,MATCH(1,(ISNUMBER(SEARCH(A1,C$1:C$50)))*(C$1:C$50<>""),0)))
Copy B1 down

 

[Guest]

Thanks Max,
I've tried this formula, but it comes up with #N/A. I've made it an array
formula but its not working. I think I may have done something wrong!
I've written it to match the coloumns I have on my spreadsheet as follows:-
{IF(D4="","",INDEX(AC$4:AC$55,MATCH(1,(ISNUMBER(SEARCH(D4,AB4:AB55)))*(AB4:AB55<>""),0)))}

Thanks again for your help!
Fiona

 

[Guest]

Here's a working sample for easy reference:
http://www.flypicture.com/download/MTU4NDA=
Fiona.xls

(note: if reading this from microsoft's webpage, do a copy n paste of the
entire link into your browser, including the "=" at the end. do not click on
the link direct.)

Array-entered in E4, press CTRL+SHIFT+ENTER to confirm the formula:
=IF(D4="","",INDEX(AC$4:AC$55,MATCH(1,(ISNUMBER(SEARCH(AB$4:AB$55,D4)))*(AB$4:AB$55<>""),0)))
Copy E4 down

I switched it around within the SEARCH. Based on a 2nd reading of the orig
post you probably have a mixture of text in your source col D. The above
should be plug-n-play, it's made based on your adaptation.

 

[Guest]

You are a genius! Thank you so much!!!

Here's a working sample for easy reference:
http://www.flypicture.com/download/MTU4NDA=
Fiona.xls

(note: if reading this from microsoft's webpage, do a copy n paste of the
entire link into your browser, including the "=" at the end. do not click on
the link direct.)

Array-entered in E4, press CTRL+SHIFT+ENTER to confirm the formula:
=IF(D4="","",INDEX(AC$4:AC$55,MATCH(1,(ISNUMBER(SEARCH(AB$4:AB$55,D4)))*(AB$4:AB$55<>""),0)))
Copy E4 down

I switched it around within the SEARCH. Based on a 2nd reading of the orig
post you probably have a mixture of text in your source col D. The above
should be plug-n-play, it's made based on your adaptation.

 

[Max]

welcome, Fiona.

 

[Guest]

Hi Max
The formula you helped me on before works perfectly, but I am trying to use
it again on another set of cells. All the information is the same, but
instead of using a list of numbers I have letters in that coloumn instead.
I'm not sure what word to change the 'number' bit in the formula too, to make
it work??
thanks again
Fiona

 

[Max]

Not quite sure how to picture the crux behind your adaptation issue above ?

Previously we had this array formula placed in E4, copied down:

=IF(D4="","",INDEX(AC$4:AC$55,MATCH(1,(ISNUMBER(SEARCH(AB$4:AB$55,D4)))*(AB$4:AB$55<>""),0)))

where the extracted numbers (placed in col E) were coming from col AC, ie
via this INDEX part: INDEX(AC$4:AC$55, .. .

If you have letters instead in col AC, then those letters should
correspondingly be extracted w/o any issue.

Could it be just a matter of the array-entering bit? Whenever you edit an
array formula (to change the ranges, etc) you need to remember to
re-array-enter it by pressing CTRL+SHIFT+ENTER (CSE) each time.

Confirm that it is correctly array-entered by looking out for the curly
braces: { } wrapped around the formula which will be inserted by Excel, in
the formula bar. If you don't see the curlies, that means it isn't
array-entered. Re-click inside the formula bar, and press the CSE again.

In our haste to proceed, the CSE part may likely be missed/improperly done.
Happens to me, too. I make it a habit to use the visual cue (the curly
braces: { }) as a check.