Thread: Index Match Function

Hi,

I am trying to use to index match function to grab values from one worksheet on two criteria, State and Year. I have data from 18 years and the setup of the worksheet with the data in it has each state listed 18 times and then the year next to each.

So when trying to use the index match function I get #N/A, is this because it is seeing multiple entries of each state and year? Is there anyway to fix this?

Basically I want it to look at the data and grab it based on matching state AND year.

I'm fairly confident that I am entering it correctly because I used it to grab data from another worksheet except the states were in column A and the year in row 1 so there was only one entry for each state/year.

Any help would be appreciated, thanks.

Hello & Welcome to the Board,

Can we see the formula you are using?

If see it being something like...

=INDEX($B$2:$J$20,MATCH("CA",A2:A20,0),MATCH(2011,$B$1:$J$1,0))

Maybe this...

=INDEX(unemp!$M$20626:$M$21632,MATCH(1,(us_data!A2=unemp!$B$20626:$B$21632)*(us_data!C2=unemp!$D$206 26:$D$21632),0))

IMPORTANT
This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

If this does not do it then please attach a small sample workbook with what you have and what you expect.

Give this a try...

http://xl-central.com/lookup-multiple-criteria.html

Here is what I am putting in now to match my workbook

=INDEX(unemp!$M$20626:$M$21632,MATCH(1,IF(unemp!B20626:B21632=A2,IF(unemp!D20626:D21632=C2,1)),0))

The only thing that I don't know the purpose of is the 1 in MATCH(1... I also haven't changed everything that should be to absolute yet but I am just trying to get it to work in the one cell for now.

EDIT: Sorry I didn't see the link you posted, I'm taking a look at it now.

EDIT 2: Also I did use CTRL+SHIFT+ENTER and got the brackets and am getting #VALUE! back.

I see you are using A2 and C2, but on the first post you had us_data!C2.

Some reason you cannot attach the workbook where you are trying this formula?

The MATCH(1 part you asked about is returning the position on the match so it can get passed to the Index function and return the result.

The best way to disect a formula is by using Evaluate formula.

Formula tab >> Formula Auditing >> Evaluate formula

By doing this you can step through the formula seeing what happens in the different stages.

Look at column D on the unemp tab. Notice the little green triangle top left corner. This usually means the cell is a number masquerading as text.

Put a zero in some cell off to the side and copy. Hightlight the range of years in column D >> right click paste special >> Add >> OK

This will take care of converting the text to number.

After converting the text to number everything worked fine, just don't forget the Ctrl + Shift + Enter.

If you might have a suspicion two cells don't match, in a cell off to the side enter =D20627=us_data!C2

This should result in False telling you the 1992 for Alabama does not match 1992 on the us_data tab.

Are you talking about what you posted in post #3?

=INDEX(unemp!$M$20626:$M$21632,MATCH(us_data!A2,unemp!$B$20626:$B$21632,0),MATCH(us_data!C2,unemp!$D $20626:$D$21632,0))

If so, this formula will not work the way you have it structured.

When using the Index and Match function it typically is used like INDEX(array,row_num,column_num)

That is, you match row and column, but you want to match row against row.

You're very welcome...glad it worked for you

If you are satisfied with the answer provided, please don’t forget to mark the thread as solved.

How to mark a thread Solved
Go to the first post
Select
-- Edit
-- Go Advanced
-- Below the word Title you will see a dropdown with the word No prefix
-- Update to Solved and then select Save