vlookup : match until a certain character is found

[nickyboy1981]

Hi guys, im quite new to excel functions but im getting there, i have an issue at the moment that im looking for help with, heres the situation.

i am trying to match data from a column to match data on another sheet so it returns a value and i know i have a match.

the eg i use will be the persons name followed by their e-mail address, i have 4000 lines on the spreadsheet and cross referencing it against 2500 hits.

im using a vlookup with false but the problem i have is that some of the data is not matching, i think it's because some of the data may have changed

so i have for eg cooke,sam (human resources) but the data im trying to match it against is cooke,sam (hr services) iknow my lookup formula is correct as it finds 900 of the 2500, but not the rest.

is there any way i can tell vlookup just to search up until the space and the ( to return an exact match for the first part?, or should inot be using vlookup?

any help would be greatly appreciated.

[Ron Coderre]

Try this example....

With this list in D1:F4

	E	F
1	cook,captain (navy)	Person
2	cook,e. monster (muppet)	Fictional Character
3	cooke,same (hr services)	Person
4	cooked,goose (entrée)	Food

and
A1: text to match.... cooke,sam (humar resources)

This formula looks in the table for text that begins with the name, ignoring everything after the name

Please Login or Register  to view this content.

In that example, the formula returns: Person

Is that something you can work with?

[José Augusto]

Hi
You can search for =VLOOKUP(LEFT(A2,FIND(" ",A2)), ...

[nickyboy1981]

thats great thanks, im sure i can have a play about with it, if it helps it's not quite as complicated as that what i am trying to do

an exact example of what im trying to is

Sheet 1

A B
1 Cooke, Sam (human resources)
2 Jones, Jim (Technology services)
3 James, Ray (IT Infrastructure)

Sheet 2
A
1 James, Ray (Information Tecnology)
2 Cooke, Sam (hr services)


I want column B in sheet 1 to return the matches, i can then filter out the n/a's and see what hit and what didnt in an easy filter

off the top of my head the formula i used in sheet 1 B is

=VLOOKUP(A1, SHEET 2!A1:A2,1,FALSE)

or something along those lines.

Thanks again for your reply, i'll let you know how i get on

[nickyboy1981]

ok heres the exact formula i used

=vlookup(RC[-4], 'TRISO'!R[-4]C[-7]:[R2649]C[-7], 1,FALSE)

RC[-4] field data i want to start with

triso and the rest is the sheet and the column i want to cross reference against.

im putting the formula in the field on the first sheet with the data i want to start with and where i want the hits to show up.

im getting all the ones that match exactly but all the ones where the department has changed i get n/a.

i tried =vlookup(left(rc[-4],'triso'!r[-4]c[-7]:r[2649]c[-7]),1,false)

and i get the VALUE errorin the return field.

hope thats not to confusing