Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default Index and match if field contains specific text in it

Hi - hope this is clear, what I want to do is look up cell contents (text) and if that word appears in another table return a value associated with that cell
ie
ColA Col B
London Borough of Croydon 109
London Borough of Ealing 110
London Borough of Enfield 104
London Borough of Greenwich 109
London Borough of Hackney 112
London Borough of Hammersmith and Fulham 115

Col C ColD (what I want!)
Croydon 109
Ealing 110
etc
Hammersmith and Fulham 115

The data comes from an external source so I am stuck with that

Any ideas (prefer to use formula rather than vba)
Thank you in anticipation
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Index and match if field contains specific text in it

Hi,

Am Thu, 29 Nov 2012 18:11:37 +0000 schrieb saintsman26:

ColA Col B
London Borough of Croydon 109
London Borough of Ealing 110
London Borough of Enfield 104
London Borough of Greenwich 109
London Borough of Hackney 112
London Borough of Hammersmith and Fulham 115

Col C ColD (what I want!)
Croydon 109
Ealing 110
etc
Hammersmith and Fulham 115


in D1 try:
=INDEX(B1:B100,MATCH("*"&C1,A1:A100,0))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Number to Text for Index/Match [email protected] Excel Worksheet Functions 2 November 3rd 12 09:33 PM
index / lookup / match / text formula JB2010 Excel Discussion (Misc queries) 4 March 28th 07 03:01 AM
index / lookup / match / text formula Toppers Excel Discussion (Misc queries) 0 March 28th 07 12:20 AM
index / lookup / match / text formula JB2010 Excel Discussion (Misc queries) 0 March 28th 07 12:18 AM
index / lookup / match / text formula Toppers Excel Discussion (Misc queries) 0 March 28th 07 12:15 AM


All times are GMT +1. The time now is 01:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"