Lookup/match formula?

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

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!
 
One way ..

Put in say, B1, and array-enter the formula, ie press CTRL+SHIFT+ENTER:
=IF(A1="","",INDEX(D$1:D$50,MATCH(1,(ISNUMBER(SEARCH(A1,C$1:C$50)))*(C$1:C$50<>""),0)))
Copy B1 down
 
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
 
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.
 
You are a genius! Thank you so much!!!

Max said:
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.
Click to expand...
 
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
 
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.
 
Back
Top