I have attached the table (Sheet 2) that I want to cross reference for my lookup (sheet 1).
For example, on Sheet 1, I will have columns A and B and a cell that says July. So, for example, in cell c3 of Sheet 1 the value would be -$3,537.24. if i change July (cell A1) to August it would give me -$126.78. If I change cell B4 to AUD in Sheet 1, it will give me -$3,537.24. Basically cross referencing everything in the table based on the three conditions. Please help. Thanks.
Last edited by Mile029; 09-07-2010 at 01:04 PM.
Your C4 formula on sheet1 would be:
=INDEX('USD AP AA'!$C$2:$N$30, MATCH($B4, 'USD AP AA'!$B$2:$B$30, 0), MATCH($A$1, 'USD AP AA'!$C$1:$N$1, 0))
I've attached a sheet showing you...
I've also shown how much easier that formula is to read when you give each section of your resource table Named Ranges. I've colored each section to point out the named ranges.
=INDEX(DataTable, MATCH($B5, Codes, 0), MATCH($A$1, Months, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Awesome that's it, I knew index, but couldn't figure out the two match statements, usually use one and three reference columns, but nice, can use two match statements, and even a third if I wanted to include the company, column A. Thanks again.
You can't easily add a third to a standard INDEX(MATCH,MATCH) technique. Easiest would be to combine the two vertical ranges values with a third column concatenating the values together to create a new index for the vertical match.
Meanwhile... If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
so what you are saying is that you can't have more than two match's in a Index statement?
You should read help files in Excel and make sure you understand the structure of an INDEX/MATCH and INDEX/MATCH/MATCH which are the two options.
INDEX/MATCH - INDEX a single column or row range then use a MATCH on a matching range in an adjacent column or row.
INDEX/MATCH/MATCH - INDEX a two-dimensional table of data, then MATCH to a range that goes down the right side of the table, this match will give you a y-axis solution in the table, then MATCH to a range going across the top of the table, this match will give you an x-axis solution in the table.
The x-axis and the y-axis are combined to give you a "position" in the INDEXed table and will return the value in that intersection.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
The two MATCH functions determine the column and row number to be used in the INDEX formula.
Therefore, no third is needed ( check the syntax for INDEX for more clarity on this matter)
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
Adding a third matching value means you're adding either another column or row of variables. In that case, I would concatenate the two adjacent criteria columns into a third single column/row, then use the concatenated column for the MATCH function.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks JB, I did the concatenation and it works great. Thanks again for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks