ExcelBanter - How do I lookup two cells and return a third.

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - How do I lookup two cells and return a third. (https://www.excelbanter.com/excel-worksheet-functions/48021-how-do-i-lookup-two-cells-return-third.html)

How do I lookup two cells and return a third.

I have data in this format:

Sheet #1
Name Year Amount
Joe 2000 100
Joe 2005 200
Sam 2003 65

And want to return Amount in another worksheet.

Sheet#2
2000 2003 2005
Joe
Sam

How do I do the lookup or match to get the data for Joe for 2000 and 2005 in
the proper cells of Worksheet#2

This is an array formula - commit it with Ctrl+Shift+Enter

=INDEX(C1:C3,MATCH("Joe"&"2005",A1:A3&B1:B3,0))

where the names are in column A, the years are in column B and the values
are in column C

"Damian" wrote:

I have data in this format:

Sheet #1
Name Year Amount
Joe 2000 100
Joe 2005 200
Sam 2003 65

And want to return Amount in another worksheet.

Sheet#2
2000 2003 2005
Joe
Sam

How do I do the lookup or match to get the data for Joe for 2000 and 2005 in
the proper cells of Worksheet#2