search in a name list

[fanch72]

Hi,

I need some help with the followin:

Sheet1 - from 20 up to 600 rows:
list of shortnames (for example petsel for Peter Sellers) in column A and a value in column B. The list is not complete so it doesn't exactly match what's on sheet2. The list is in alphabetic order

A B
paulan 2
petsel 5
...

Sheet2 - about 600 rows:
translation list: with full name and surname (column A) and shortname (column B)

A B
...
Paul Landau paulan
Peter Sellers petsel
...

I need to compare sheet1 and sheet2 so that I get the full name and surname and the corresponding value.

A B
Paul Landau 2
Peter Sellers 5

Can someone help me with that?

Thanks

Francois

 

[GlennUK]

look at the VLOOKUP function.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[fanch72]

I have tried it, but it gives some very strange results! About half the list is correct and the rest is completely wrong. The strange thing is that I can't find a pattern in the way Excel picks the wrong value!

 

[fanch72]

fixed now... I forgot that the table had to be sorted properly.