lookup value with two criteria with array

[faizee]

hi
can anyone help me in following lookup value with multiple criteria, but i want with array
plus, which is faster. array formula or vlookup?

date	code	Rate
25-May-21	613974	0.376
24-Jun-21	613974	0.376
15-Sep-21	613974	0.376
20-Sep-21	613974	0.376
29-Oct-21	613974	0.39
21-Dec-21	613974	0.39
18-Mar-22	613100	0.444444
18-Mar-22	613974	0.421
11-Apr-22	613974	0.421
date	18-Mar-22
code	613974
Rate		(need to find this with array(

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[faizee]

excel 2016

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

Thanks for updating your profile.

This is how I would do it with your Excel version.

22 06 14.xlsm
	A	B	C
1	date	code	Rate
2	25-May-21	613974	0.376
3	24-Jun-21	613974	0.376
4	15-Sep-21	613974	0.376
5	20-Sep-21	613974	0.376
6	29-Oct-21	613974	0.39
7	21-Dec-21	613974	0.39
8	18-Mar-22	613100	0.444444
9	18-Mar-22	613974	0.421
10	11-Apr-22	613974	0.421
11
12
13	date	18-Mar-22
14	code	613974
15	Rate	0.421
Lookup

Cell Formulas
Range	Formula
B15	B15	=INDEX(C:C,AGGREGATE(15,6,ROW(C2:C10)/((A2:A10=B13)*(B2:B10=B14)),1))

 

[faizee]

Thank you so much Mr Peter..
i also used following formula. could you please if the records are more than 10,000 rows. so which formula will work faster. your or mine?

date	code	Rate	date&code
25-May-21	613974	0.376	44341613974
24-Jun-21	613974	0.376	44371613974
15-Sep-21	613974	0.376	44454613974
20-Sep-21	613974	0.376	44459613974
29-Oct-21	613974	0.39	44498613974
21-Dec-21	613974	0.39	44551613974
18-Mar-22	613100	0.44444	44638613100
18-Mar-22	613974	0.421	44638613974
11-Apr-22	613974	5555	44662613974
date	18-Mar-22
code	613100
Rate	=INDEX(C2:C10,MATCH(B13&B14,D2:D10,0),1)

 

[Peter_SSs]

i also used following formula.

It may be extremely unlikely with your data, but that formula could produce incorrect results under some circumstances. For example, with the dummy data below it returns 0.55555 and not the correct figure of 0.44444

22 06 14.xlsm
	A	B	C	D
1	date	code	Rate	date&code
2	25-May-21	613974	0.376	44341613974
3	24-Jun-21	613974	0.376	44371613974
4	20-Mar-12	8613100	0.55555	44638613100
5	20-Sep-21	613974	0.376	44459613974
6	29-Oct-21	613974	0.39	44498613974
7	21-Dec-21	613974	0.39	44551613974
8	18-Mar-22	613100	0.44444	44638613100
9	18-Mar-22	613974	0.421	44638613974
10	11-Apr-22	613974	5555	44662613974
11
12
13	date	18-Mar-22
14	code	613100
15	Rate	0.55555
Lookup (2)

Cell Formulas
Range	Formula
A4	A4	=4463
D2:D10	D2	=A2&B2
B15	B15	=INDEX(C2:C10,MATCH(B13&B14,D2:D10,0),1)

if the records are more than 10,000 rows. so which formula will work faster. your or mine?

You could test that as easily as I could.