Index and Match: Not working - why?

[chloec]

Goal: Use Lookup to find intersection of Style and Supplier – Return result the corresponding “date”. This result is shown in the last column to the right. If two dates appear with the same supplier /date (like 3-Abby) return the most recent date. I tried this and it doesn’t work: =INDEX(C:C,MATCH(E2,$A:$A,0),MATCH(D2,$B:$B,0))


Data:

ColA-Supplier ColB-Style ColC-Date

Abby Style1 2/27/2009

Bobby Style2 2/27/2009

Charlie Style2 3/26/2009

Doug Style4 3/20/2009

Abby Style2 3/26/2009

Abby Style3 3/31/2009

Abby Style3 3/30/2009

Charlie Style3 6/9/2009

----------------------------------

ColD-Lookup_Style / ColE-Lookup_Supplier / Result

Style2/Abby/3/26/2009

Style3/Abby/3/31/2009

Style3/Charlie/6/9/2009

Style5/PETER/N/A


Thank you!! I think I posted a blank question previously! That one can be deleted. Thanks and sorry!

 

[SirJB7]

Hi, chloec!


Try this array formula (entered with Ctrl-Shift-Enter) in F2, and then copy down to F3:F5 :

{=MAX(IF((A:A=E2)*(B:B=D2)=0,"",C:C))}


Regards!

 

[Faseeh]

Hi chloec,


This one will also work, enter in F2 and drag down:

=LARGE(($A$2:$A$9&$B$2:$B$9=E2&D2)*($C$2:$C$9),1)


NB: If found nothing it will show zero and since your column F is formatted as date it will show you 1/0/1900. In order to get rid of this 1/0/1900, select column F and apply this custom cell formatting: 


mm/dd/yy;"";"--"

..Now if a zero is encountered it will replaced by '--'(This Cell Formatting works equally for SirJB7's formula)


Regards,

Faseeh

 

[chloec]

Fantastic! Thank you so much! What is the purpose of the "*" ? I don't get how that works..can you please explain!? Thanks so much! Also, what if I want to throw in a third criteria - would that work as well?

 

[SirJB7]

Hi, chloec!


In the formula I posted (which I like much more than Faseeh's longer and ugly formula, but don't care, he always does such things, don't you Faseeh?)... where were we? Oh, yes.

In my case the "*" is used for multiplying position by position the array resulting of comparing the range A:A with the value in E2 (which returns {true, false, false, false, true, true, true, false} -if you change the named range length from A:A to A$2:A$9, you press F2 to edit formula, you select (A$2:A$9=E2) and press F9, you'd see that values in the edited formula, with the whole A:A it has a length of more than 8192 characters and Excel can't display it, because of the 1048576 values for each cell-) with the same array for B:B = D2. The IF is for avoid changing formats to not display 00/01/1900, and C:C is the column to show the date.

In Faseeh's case it just operates in the same way, except he has concatenated values of column A and B for comparing against E2 concatenated with D2, and then performed the "multiplication" against dates array.

And if you insert more conditions, just be careful but in both cases it will still do the job: in the first one insert "*(X:X=Y2)" unquoted between the ")" before the equal sign and the equal sing; in the second one insert "&$X$2:$X$9" before the equal sign and "&Y2" between "D2" and ")". Assuming your new column condition is in column X and your new selected data for testing that condition is in column Y.


Just advise if any trouble or further details.


Regards!