Search for a match on a different sheet and if true then copy a cell from the sheet

[Madjock]

Hi,
I am going round and round but not getting the results I am after.

I have 2 worksheets. Worksheet 1 contains a list of reference numbers, and names. Worksheet 2 contains a list of invoices along with reference numbers. The reference numbers are the common link between the 2 sheets.

On the 2nd worksheet I have a column and I need it to be populated by the correct name from worksheet 1. I need it to search worksheet 1 for the same reference number and when it does copy the value of the name cell on the same row to sheet 2.

eg:
Sheet 1

A	B
1234	John Smith
1292	Tom Jones
1254	Bill Smith
1341	A. Nother

On worksheet 2:

A	B
100.00	1341
19.99	1292
27.14	1254

Hope this makes sense.

Thanks

Matt

[Fotis1991]

You can use INDEX & MATCH functions.

http://www.contextures.com/xlFunctions03.html


Edit: Something like this.

=INDEX(Sheet1!B:B,MATCH(Sheet2!B1,Sheet1!A:A,0))

[Madjock]

Thanks for your quick reply.

I entered =INDEX('Download Purchase Lines'!H:H,MATCH(B4,'Download Purchase Lines'!C:C,0))

I also tried: =INDEX('Download Purchase Lines'!H:H,MATCH('June 2012'!B4,'Download Purchase Lines'!C:C,0))

But all I am getting is N/A

I have checked the common reference number is present.

Am I missing something obvious?

Man thanks again

Matt

[Fotis1991]

...I have checked the common reference number is present.

Am I missing something obvious?

Then, pls, upload a small sample workbook.

[Madjock]

Hi,

Many thanks again. I have attached some sample data.

Thanks again

Matt

[Fotis1991]

Try this and try to understand how formula works.

=INDEX('Download Purchase Lines'!B:B,MATCH($B4,'Download Purchase Lines'!G:G,0))

[Madjock]

Many thanks Fotis,

Is there any reason I would still get N/A when I run it on full live data, but not when use the test data I created?

Thanks again

Matt

[Fotis1991]

Yes. You get N/A, where match, find nothing!

Try

=iferror(INDEX('Download Purchase Lines'!B:B,MATCH($B4,'Download Purchase Lines'!G:G,0)),"")

[Madjock]

You are brilliant.

Many thanks.

Matt

[Fotis1991]

You are welcome Matt!

Thanks for the reb*