Forum Discussion
looking up names in seperate excel lists
I work in HR and I frequently have to cross reference lists with names of Employees. Often the names are written in a different order from list to list i.e. List A: First name[space]Family name the...
Hi William
Yes, that's not ideal. Your variant could work. If your lists are always in same format, i'd suggest to use INDEX MATCH combination. If you have
A B C D
1
2 List1 List2 Position in List1
3 John Smith Flynn William 3
4 Bill Gates Smith John 1
5 William Flynn Gates Bill 2
formula inserted in D3
= MATCH(
RIGHT($D3,LEN($D3)-FIND(" ",$D3)) &
" " &
LEFT($D3,FIND(" ",$D3)-1),
B:B,0)
- ROW($B$2)
and copied to D4:D5 gives you numbers as above.
If you List have another column from which you'd like to pick-up values to List2 use =INDEX(ref column, MATCH(as above)). And instead of lists better to use tables.
Sorry, Reply editor disappeared here, could use Quick Reply only.
Yes, that's not ideal. Your variant could work. If your lists are always in same format, i'd suggest to use INDEX MATCH combination. If you have
A B C D
1
2 List1 List2 Position in List1
3 John Smith Flynn William 3
4 Bill Gates Smith John 1
5 William Flynn Gates Bill 2
formula inserted in D3
= MATCH(
RIGHT($D3,LEN($D3)-FIND(" ",$D3)) &
" " &
LEFT($D3,FIND(" ",$D3)-1),
B:B,0)
- ROW($B$2)
and copied to D4:D5 gives you numbers as above.
If you List have another column from which you'd like to pick-up values to List2 use =INDEX(ref column, MATCH(as above)). And instead of lists better to use tables.
Sorry, Reply editor disappeared here, could use Quick Reply only.
Hi Sergei,
Thanks but I couldn't get your formula to work. You said to put the formula in D3 but there was also a reference to D3 in the formula so I got a circular reference.
You also appear to have a column that gives the location of the value in the "to be compared" column. How can this be achieved when you have hundreds of names that are constructed differently (just the problem I am trying to resolve).
Probably just me being a bit slow on the pick up so I apologise.
kind regards,
Williiam
- Without Rich Editor can't add the sample, i'll send you in-private message bit later. If it works, never tried here.
Fantastic, editor appeared back. Anyway, i've already sent the link with in-private message.
