Match two columns (two sheets) to pull data into sheet 1

[mmayna03]

Good afternoon,

I am using Excel 2007.

I have frustrated myself trying to make a sample formula work I received from a friend.

I need the formula to extract an order number (DOCNO) from Sheet2's column D to put its matching record on Sheet1 in column A when Sheet1's NIIN matches Sheet2's NIIN column.

I have attached a sample of the spreadsheet.

Here is the script sent me. I made multiple variations trying to make it work, but no success.

=INDEX(Sheet1!$C$2:$C$2493,MATCH(1,IF(Sheet1!$A$2:$A$2493=$B4,IF(Sheet1!$B$2:$B$2493=$C4,1)),0))

Thanks for any help.

Michael

Sample.xlsx

[oeldere]

See the golden cells.

Please reply if this is what you're up to.

[mmayna03]

Awsome, seeing what you did and my changing the sheet names to update the formula shows perfectly how to set up the formula. I did expand the column (bolded) to use on the rest of the rows.


=INDEX(DOCNO!D$1:F$6,MATCH(ABF_Records!$D$6:D22,DOCNO!$F$1:$F$20,0),1)


Thank you very much, it works great.

Michael

[oeldere]

Learn yourself to keep the range of data always the same. e.g. d1:f20 => d6:d20 => f1:f20 (instead of f21).

=INDEX(DOCNO!D$1:F$20,MATCH(ABF_Records!$D$6:D20,DOCNO!$F$1:$F$20,0),1)

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?

If you have (other) questions, on this item, just ask.