Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
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(SEAR CH(A1,C$1:C$50)))*(C$1:C$50<""),0))) Copy B1 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fiona" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
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(SE ARCH(D4,AB4:AB55)))*(AB4:AB55<""),0)))} Thanks again for your help! Fiona "Max" wrote: 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(SEAR CH(A1,C$1:C$50)))*(C$1:C$50<""),0))) Copy B1 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fiona" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
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(SE ARCH(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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Fiona" wrote: 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(SE ARCH(D4,AB4:AB55)))*(AB4:AB55<""),0)))} Thanks again for your help! Fiona |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
You are a genius! Thank you so much!!!
"Max" wrote: 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(SE ARCH(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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Fiona" wrote: 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(SE ARCH(D4,AB4:AB55)))*(AB4:AB55<""),0)))} Thanks again for your help! Fiona |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
welcome, Fiona.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fiona" wrote in message ... You are a genius! Thank you so much!!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
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" wrote: 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(SE ARCH(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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Fiona" wrote: 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(SE ARCH(D4,AB4:AB55)))*(AB4:AB55<""),0)))} Thanks again for your help! Fiona |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup/match formula?
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(SE ARCH(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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Fiona" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP, INDEX, MATCH (?) Formula | Excel Worksheet Functions | |||
index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
Excel Lookup MATCH formula | Excel Discussion (Misc queries) | |||
lookup? Index? match? formula | Excel Discussion (Misc queries) |