Excel. Lookup function not the answer.

  John B 10:12 14 Jan 2006
Locked

I am trying to get an excel worksheet to look at another sheet and if data corresponds, insert the information in the first sheet.

Sheet1 contains about 1000 rows of data, column A has a list of 5-digit ascending numbers which are not concurrent (e.g. 12054, 12087, 13078 etc).

Sheet2 contains about 150 rows of data and again column A has a list of 5-digit ascending numbers which are not concurrent. These numbers are the same as in sheet1 (although not all of sheet1’s numbers appear in sheet2).

I want sheet1 to look at sheet2 and if sheet1 finds a number match on sheet2, enter the contents of sheet2’s column B into a column in sheet1. If no number match is found, leave the cell in sheet1 empty.

I have tried to use Lookup function which works after a fashion, but when a cell in sheet1 can’t find a match in sheet2 it enters rubbish data in the sheet1 cell.

Is there a way to do this please?

Sorry for the lengthy text but I find it difficult to describe the layout!

Thanks

John

  VoG II 10:25 14 Jan 2006

Along the lines of

=IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,FALSE)),"",VLOOKUP(A1,Sheet2!A1:B5,2,FALSE))

  John B 10:29 14 Jan 2006

I'll give that a go and get back later.

John

  John B 10:58 14 Jan 2006

Thanks again VoG

That worked a treat! I spent hours yesterday trying to solve that problem. Marvellous!

Cheers

John

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?