How to vlookup with 2 criteria's (a direct matching criteria and an approximately matching criteria)?

[karthick019]

Dear all,

My question is based on the sample table below,

[TABLE="width: 219"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Date[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]01/01/2010[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]01/05/2010[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]01/01/2011[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]01/03/2012[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]01/04/2013[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]01/04/2010[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]01/06/2011[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]01/08/2012[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]01/09/2012[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]01/04/2013[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


I need a formula with 2 criteria's where part number is matched directly and i have a date and need to find the closest minimum range and update the price for the same.

Eg,

When I enter Part number as "A" and Date as "05/09/2012". the price should be matched as below,
(Part=A) & (Date = closest minimum date) ie. "01/03/2012" in this case.

[TABLE="width: 238"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Date[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]05/09/2012[/TD]
[TD]400[/TD]
[/TR]
</tbody>[/TABLE]

I tried searching for this but I am left with no answers. Help me out excel experts.

 

[Daveydoodles]

With your data in columns A:C, the part to search for in E1 and the date to search for in F1;

=INDEX(C:C,MATCH(F1,IF(A:A=E1,B:B),1))

array entered with CTRL+SHIFT+ENTER

Does this work?

 

[Peter_SSs]

Welcome to the MrExcel board!

If the dates for each part number are in ascending order, as yours appear to be, then Daveydoodles formula I think should do the job, though I would definitely not use whole column references. No use checking 1,000,000+ rows if you are only using 100 or so for example.

If you are using Excel 2010 or later you can use the standard formula in F3 below.
Otherwise, the formula in F4 which will work in all versions and would still work if the dates are not in ascending order. It is also an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C	D	E	F
1	Part	Date	Price		Part	A
2	A	1/01/10	100		Date	5/09/12
3	A	1/05/10	200		Price	400
4	A	1/01/11	300			400
5	A	1/03/12	400
6	A	1/04/13	100
7	B	1/04/10	600
8	B	1/06/11	700
9	B	1/08/12	800
10	B	1/09/12	900
11	B	1/04/13	100
12
Lookup Value

 

[karthick019]

With your data in columns A:C, the part to search for in E1 and the date to search for in F1;

=INDEX(C:C,MATCH(F1,IF(A:A=E1,B:B),1))

array entered with CTRL+SHIFT+ENTER

Does this work?

Wow Daveydoodles. That worked like a charm.
I was spending hours to figure this out... Thanks a lottt mate.

This s a very good learning for me.

 

[karthick019]

Welcome to the MrExcel board!

If the dates for each part number are in ascending order, as yours appear to be, then Daveydoodles formula I think should do the job, though I would definitely not use whole column references. No use checking 1,000,000+ rows if you are only using 100 or so for example.

If you are using Excel 2010 or later you can use the standard formula in F3 below.
Otherwise, the formula in F4 which will work in all versions and would still work if the dates are not in ascending order. It is also an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Lookup Value

	A	B	C	D	E	F
Part	Date	Price		Part
A		Date
A		Price
A
A
A
B
B
B
B
B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"><col style="width:81px;"><col style="width:56px;"><col style="width:56px;"><col style="width:85px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]A[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1/01/10[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]5/09/12[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1/05/10[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1/01/11[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1/03/12[/TD]
[TD="align: right"]400[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1/04/13[/TD]
[TD="align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]1/04/10[/TD]
[TD="align: right"]600[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1/06/11[/TD]
[TD="align: right"]700[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1/08/12[/TD]
[TD="align: right"]800[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1/09/12[/TD]
[TD="align: right"]900[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]1/04/13[/TD]
[TD="align: right"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F3 =AGGREGATE(14,6,(A2:A11=F1)*(B2:B11<=F2)*C2:C11,1) F4 {=MAX(IF(A2:A11=F1,IF(B2:B11<=F2,C2:C11)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Peter & Daveydoodles. You both are genius.

Thanks a lot for your very quick reply. I'll try out your function as well... Thanks again...!!

 

[Daveydoodles]

You're welcome

As Peter said, it will only work when the dates are in ascending order like in your sample