LinkBack URL
About LinkBacks
I need to return the value in the table array even if it only [I]contains part of [I] the look up value. eg look up value is BD1-22 and array has a cell with text of BD.
I need to return the value in the table array even if it only [I]contains part of [I] the look up value. eg look up value is BD1-22 and array has a cell with text of BD.
INDEX and MATCH
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
Try something like: MATCH("*"&B1&"*",A1:A20,0)
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
do you mean =vlookup("BD-21",A1:B100,2,FALSE) but column A may only have BD in it?
you could get the corresponding value from col b like this
=LOOKUP(10^99,SEARCH(A1:A300,"bd-21")/(A1:A300<>""),B1:B300)
Last edited by martindwilson; 06-11-2014 at 10:09 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Nathan, Jacc and Martin
You are probably right but would you be able to show how your suggestion works in the simple example attached? I need the value of £37.65 to populate cell B2. Many thanks.
Example.xlsx
Last edited by Thistledown; 06-11-2014 at 11:13 AM.
Please see attached file with formula.
Did this work for you?
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Funny. I just solved the same problem minutes ago.
Take a look at my worksheet posted here: http://www.excelforum.com/excel-form...act-match.html
Thank you AlKey and Jacc. Awesome forum!
You're welcome. Don't forget to thank those who helped by clicking on [B]Add Reputation *
with what alkey has suggested you will have problems as L for liverpool would not work
unfortunately there are some single letter post codes eg E,L,M to get around that you can use
=IFERROR(LOOKUP(10^99,SEARCH(" "&$F$2:$F$122," "&A2),$G$2:$G$122),"NOT FOUND")
also it would help if you simply listed all post code prefixes there are only for all practical purposes 121
note the list need to be for each starting letter smallest to largest so B comes before ba,bb,bd
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks