Index Formula with Multiple Match Criteria

[Melo]

I am trying to create a formula that will return a record number (in column A on "Report" sheet) based on matching both a last name (C4) and date (E4) with a corresponding last name (column AE) and date (column B) on a second sheet ("Report"). This is what I have so far:

=INDEX(Report!$A:$AE,MATCH(C4&E4,Report!$AE:$AE&Report!$B:$B,0),1)

I keep getting a #VALUE error. I don't know what I am doing wrong. Please Help!!

 

[Aladin Akyurek]

I am trying to create a formula that will return a record number (in column A on "Report" sheet) based on matching both a last name (C4) and date (E4) with a corresponding last name (column AE) and date (column B) on a second sheet ("Report"). This is what I have so far:

=INDEX(Report!$A:$AE,MATCH(C4&E4,Report!$AE:$AE&Report!$B:$B,0),1)

I keep getting a #VALUE error. I don't know what I am doing wrong. Please Help!!

Return a record from where (which column) exactly?

 

[jai9]

Hi,

Try Sumproduct

=SUMPRODUCT(--(AE:AE=C4)*--(B:B=E4)*(A:A))

Jai

 

[Aladin Akyurek]

Hi,

Try Sumproduct

=SUMPRODUCT(--(AE:AE=C4)*--(B:B=E4)*(A:A))

Jai

http://www.mrexcel.com/forum/excel-questions/202204-find-last-cell-data-formular.html

elaborates on the coercers like --- and *.

 

[Melo]

I want it to return a record number from column A on sheet "Report"

 

[jai9]

Find last cell with data - as formular

elaborates on the coercers like --- and *.

Hi Aladin,

Thanks for the link.

My excel doesnt seem to like sumproduct as

=SUMPRODUCT(--(AE:AE=C4),--(B:B=E4),(A:A))

So I have to use

=SUMPRODUCT(--(AE:AE=C4)*--(B:B=E4)*(A:A))

Maybe the European and US standards.

Jai

 

[Melo]

So the database that I downloaded my reference ("Report") sheet out of had all of the date entries as text. UGGGGG!!!! Your formula is working perfectly now. Thank you!! Wish that hadn't taken 4 hours to figure out.

 

[jai9]

What does this return?

=SUMPRODUCT(--(AE1:AE1000=C4)*--(B1:B1000=E4)*(A1:A1000))

Jai

 

[Aladin Akyurek]

I want it to return a record number from column A on sheet "Report"

Control+shift+enter, not just enter:

=MATCH(C4,IF(Report!$B$2:$B$500=E4,Report!$AE$2:$AE$500),0)

yields the record number.

Control+shift+enter, not just enter:

=INDEX(Report!$A$2:$A$500,MATCH(C4,IF(Report!$B$2:$B$500=E4,Report!$AE$2:$AE$500),0))

yields the record itself.