Hi -
I keep getting a reference error with my index match formula.
I have a summary page that includes a date and a name "xx", which I'm trying to match to my data tab. The "xx" is listed in a column and the date is listed in the row.
=INDEX(Sheet2!B3:G3,MATCH(Sheet1!B1,Sheet2!A2:A23,0),MATCH(Sheet1!A3,Sheet2!A1:G1,0))
THX!
Last edited by MJ06; 11-17-2016 at 04:15 PM.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Thanks! Posted an attachment.
Formula:
Please Login or Register to view this content.
Your INDEX range needs to cover both the row and column ranges, so it needs to be at least...Originally Posted by MJ06
=INDEX(Sheet2!$B$2:$G$23,MATCH(Sheet1!B1,Sheet2!$A$2:$A$23,0),MATCH(Sheet1!A3,Sheet2!$A$1:$G$1,0))
Note also how I have "absoluted" the ranges usung $
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The problem is this
=INDEX(Sheet2!B3:G3,MATCH(Sheet1!B1,Sheet2!A2:A23,0),MATCH(Sheet1!A3,Sheet2!A1:G1,0))
Your indexed range is only 1 row, B3:G3
But your first match is 22 rows A2:A23
So if that Match is any number other than one, say 2, then it becomes
=INDEX(B3:G3,2...
There is no 2nd row of B3:G3
That indexed rang needs to match the number of rows in the first match, and the number of columns in the 2nd match.
=INDEX(Sheet2!B2:G23,MATCH(Sheet1!B1,Sheet2!A2:A23,0),MATCH(Sheet1!A3,Sheet2!A1:G1,0))
Thanks so much!!!!
You're welcome.
Happy to help
Here's another one...
=VLOOKUP(B$1,Sheet2!A$1:G$23,MATCH(A3,Sheet2!A$1:G$1,0),0)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Actually, the 2nd match has a similar problem..
=INDEX(Sheet2!$B$2:$G$23,MATCH(Sheet1!B1,Sheet2!$A$2:$A$23,0),MATCH(Sheet1!A3,Sheet2!$A$1:$G$1,0))
The 2nd match is referencing A1:G1, but the indexed range is B1:G23
Again, MORE columns in the matched Range (7) than there are in the INDEXED range (6)
Probably should be
=INDEX(Sheet2!$B$2:$G$23,MATCH(Sheet1!B1,Sheet2!$A$2:$A$23,0),MATCH(Sheet1!A3,Sheet2!$B$1:$G$1,0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks