• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index(Match,Match) formulas not working

J

Jelizabug

New Member
I'm re-vamping a spreadsheet our company is using to perform audits at each of our ten locations. Each location will be audited in different areas depending on their contract requirements, etc. I have a list of audit questions assigned to different audit areas, and I'm trying to do a lookup using multiple criteria to note whether an audit question is applicable to a particular location.


I thought that using a combination of index and match would solve my problem, and at first it looked like it was working. However, on further testing, it turns out that the formula ONLY works for the first cell - even though the formula is identical in the cells below and to the right. On my example spreadsheet, Cell J8 is looking for whether Area 1 applies to Location 1. When I change the reference table cell from "Yes" to "No," it updates J8. However, none of the other cells is updating correctly.


I've double checked Named Ranges, spellings, formulas, everything I can think of, and I can't figure out what I'm missing. When my supervisor and I were looking at the formula using f(x), it looks like the formula is finding multiple matches to Location or Area. Since this is the first time I've really used the Index and Match formulas, I'm utterly confused and would love some help.


Here's what my formula looks like:


=INDEX(AuditStandardsTable,MATCH(J$7,AuditLocations,0),MATCH($D8,AuditAreas,0))


The named ranges are on the "Lists" tab. On the Audit Questions tab, I've copied over the Audit information table for a quick check to see if the formulas are updating correctly.


Hopefully this download link will work... http://speedy.sh/Z9698/Example.xlsx


Please let me know if this isn't clear or you need more information - it's my first post to an excel help board (whee), so I might have missed something that will be helpful. :)


Thanks!!
 
Jelizabug


Firstly, Welcome to the Chandoo.org forums.


You had the Rows and Columns mixed up in the Index function

Swap them around or use

In J8:
Code: 
=INDEX(AuditStandardsTable,MATCH($D8,AuditAreas,0),MATCH(J$7,AuditLocations,0))

Copy down & Across
 
Oh my goodness, thank you soooo much! Works perfectly now - I was afraid I'd be missing something super-easy. I have been following Chandoo's posts for quite a while now - I'll have to make use of the forums more often. :)
 
You must log in or register to reply here.
Back
Top