Locate value using 2 criteria

[donk04]

Hi to everyone.

I have an excel where in column C i have dates (7/1/2016, 6/1/2016, 5/1/2016, 4/1/2016,...etc), in column D i have currencies (USD,GBP, INR, ZAR, ..., etc.), and in column E the values.

In a second sheet in A1 i have the date, in A2 the currency, so I need in A3 to bring the value searching from the first sheet.

A1 7/1/2016
A2 USD
A3 ?


Thanks in advance for your valuable help,
Manos

[JohnTopley]

Try in A3

=INDEX(Sheet1!E2:E100,MATCH(1,(A1=Sheet1!C2:C100)*(A2=Sheet1!D2:D100),0))

[JohnTopley]

Try in A3

=INDEX(Sheet1!E2:E5,MATCH(1,(A1=Sheet1!C2:C5)*(A2=Sheet1!D2:D5),0))

Enter with Ctrl+Shift+Enter

[JohnTopley]

Or

=INDEX(Sheet1!E2:E5,AGGREGATE(15,6,ROW(A1:A5)/(Sheet1!C2:C5=A1)/(Sheet1!D2:D5=A2),ROW(A1)))

[donk04]

Hi John,
thank you for your help.

The second criteria gives false. The Currency is repeated each day so i have 365 * USD, 365* BGN,.
The first one is ok with the date.

[hemesh]

here is another one
=INDEX(Sheet1!$E$1:$E$4,MATCH(TRUE,INDEX(A1&A2=Sheet1!$C$1:$C$4&Sheet1!$D$1:$D$4,0),0))
change the reference accordingly

[donk04]

That works fine.
Thanks a lot.

[JohnTopley]

Unless I misunderstand the format of your data, both work for me

07/01/2016 USD 123
07/01/2016 BGN 234


With data as shown I get 123 a1=07/01/2016 and a2=USD

or 234 with a2=BGN

[Glenn Kennedy]

You would do better to look again at John's formula. Those concatenation array forms of index-match (Post 6) can be very slow if you have a lot of rows!!!

[hemesh]

you can use below as an another approach
=VLOOKUP(A1&A2,CHOOSE({1,2},Sheet1!$C$1:$C$4&Sheet3!$D$1:$D$4,Sheet1!$E$1:$E$4),2,0)
copy paste above then hold ctrl+shift together and then hit enter
change reference accordingly

[hemesh]

=MAX(INDEX((A1=Sheet1!C1:C4)*(A2=Sheet1!D1:D4)*(Sheet1!E1:E4),0))

how about this one then

in case of no duplicates
=SUMIFS(Sheet1!E:E,Sheet1!C:C,A1,Sheet1!D:D,A2)