Index Match Question

[TyeReece]

I have the following formula that returns data but is returning the wrong data. I can't seem to see what is wrong with it. Any help is appreciated.

=IF(ISERROR(INDEX(ALPSTable,MATCH(1,IF($C$11=ALPDates,IF($E$2=ALPSVNUM,1)),0),3)),0,INDEX(ALPSTable,MATCH(1,IF($C$11=ALPDates,IF($E$2=ALPSVNUM,1)),0),3))

In this formula $c$11 is a date (11/1/2016) and $E$2 is a vlookup formula that returns a vendor number (e.g., 0611121) based on a name that is selected from a drop down list is cell a2. The data that gets returned seems to be from a close vendor number but not the correct one. For example if I select vendor "Fred's Bank" which has the vendor number 0611121 the data that gets returned is for vendor # 0611125. Any ideas as to what is wrong with this?

 

[barry houdini]

Your formula is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER, or try this "non-array" version

=IFERROR(INDEX(ALPSTable,MATCH(1,INDEX(($C$11=ALPDates)*($E$2=ALPSVNUM),0),0),3),0)

 

[TyeReece]

Yes. I have 11 other cells in the array as I am using it to create a line graph for a dashboard. When I enter the non array version it still gives the same wrong data.

 

[barry houdini]

The formula relies on all the named ranges starting on the same row, so I'd check how ALPSTable, ALPDates and ALPSVNUM are defined - do they all start from the same row?

 

[TyeReece]

ALPSTable is the entire table of the ALPS data. There are five columns in the table. Vendor Number/Date/Sumofontime/countofontime/Percentage. ALPDates is the column "Date" and ALPSVNUM is the column "Vendor Number". So if I understand your question correctly, then yes they do start on the same row.

 

[barry houdini]

Without actually seeing your data then mismatched ranges seems the only likely cause to me - does the whole table contain the headers and the other ranges not? In that case then you'd probably get the result from a row below where it should be.

For debugging purposes I would try the same formula using actual cell references and see if that works

 

[TyeReece]

Thanks Barry. I will try that. I am new to index/match so it is taking me a while.