Looking up data - Match / Index / Other ????

[601488]

Help!

I have a table of data (table 1) in a spreadsheet looking something like this:

Month EUR AUD USD CAD
Apr-11 1.1322 1.5465 1.6347 1.5662
Mar-11 1.1542 1.5992 1.6162 1.5787
Feb-11 1.1808 1.5979 1.6116 1.5919
Jan-11 1.1797 1.5793 1.5755 1.5659
Dec-10 1.1809 1.5743 1.5606 1.5746
Nov-10 1.167 1.6145 1.5984 1.6175
Oct-10 1.1412 1.6157 1.5858 1.6144

In another table (table 2) of the spreadsheet I have a 10,000 row spreadsheet containing several columns of data for each row. Two of the pieces of data are the Month and Currency.

If, for example, in table 2 on one of the rows of data I have Feb-11 and the AUD how can I get excel to lookup 1.5979 (the rate for the month in table 1) and return this in a cell in table 2?

Thanks in advance for any help!
Jon

 

[VoG]

Try like this

Excel Workbook
	A	B	C	D	E	F	G
1	Month	EUR	AUD	USD	CAD		Feb-11
2	Apr-11	1.1322	1.5465	1.6347	1.5662		AUD
3	Mar-11	1.1542	1.5992	1.6162	1.5787		1.5979
4	Feb-11	1.1808	1.5979	1.6116	1.5919
5	Jan-11	1.1797	1.5793	1.5755	1.5659
6	Dec-10	1.1809	1.5743	1.5606	1.5746
7	Nov-10	1.167	1.6145	1.5984	1.6175
8	Oct-10	1.1412	1.6157	1.5858	1.6144
Sheet1

 

[Aladin Akyurek]

Help!

I have a table of data (table 1) in a spreadsheet looking something like this:

Month EUR AUD USD CAD
Apr-11 1.1322 1.5465 1.6347 1.5662
Mar-11 1.1542 1.5992 1.6162 1.5787
Feb-11 1.1808 1.5979 1.6116 1.5919
Jan-11 1.1797 1.5793 1.5755 1.5659
Dec-10 1.1809 1.5743 1.5606 1.5746
Nov-10 1.167 1.6145 1.5984 1.6175
Oct-10 1.1412 1.6157 1.5858 1.6144

In another table (table 2) of the spreadsheet I have a 10,000 row spreadsheet containing several columns of data for each row. Two of the pieces of data are the Month and Currency.

If, for example, in table 2 on one of the rows of data I have Feb-11 and the AUD how can I get excel to lookup 1.5979 (the rate for the month in table 1) and return this in a cell in table 2?

Thanks in advance for any help!
Jon

Let Sheet1, A1:E8, house the table you posted, the headers included.

Sheet2

Let A2 house a month/year of interest, B2 a currency of interest.

In C2 enter:

=INDEX(Sheet1!$B$2:$E$8,MATCH(A2,Sheet1!$A$2:$A$8,0),MATCH(B2,Sheet1!$B$1:$E$1,0))

 

[601488]

Many thanks - a speedy response that works - hoorary!