Vlookup formula require for multiple criteria

**amolryeolekar** · 01-25-2013, 02:00 AM

I want the 2 criteria formula which can give near about match of above clumn C and D values from sheet1 DB and display here in yellow colur cells.
but when i use index match formula causing huge time if i seek lots of values and easy formula which can pull the values from DB

thanx

**dilipandey** · 01-25-2013, 04:04 AM

Hi amolryeolekar,

And what should be the output ?

Also, how you want to proceed - first lookup c3 in column B(sheet1) and then lookup D3's value in column C (sheet1) OR other way round ?

Regards,
DILIPandey
<click on below * if this helps>

**e.isayev** · 01-25-2013, 04:07 AM

Hi,
Unfortunately formula is long but I am sure, Excel gurus will suggest you smarter alternative. Here is my array formula
e3 cell> =INDEX(sheet1!$K$3:$K$81,MATCH(SMALL(ABS(IFERROR((SMALL(IFERROR(ABS((sheet1!$B$3:$B$81-Sheet2!C3)),""),1)=IFERROR(ABS((sheet1!$B$3:$B$81-Sheet2!C3)),""))*sheet1!$C$3:$C$81,0)-Sheet2!D3),1),ABS(IFERROR((SMALL(IFERROR(ABS((sheet1!$B$3:$B$81-Sheet2!C3)),""),1)=IFERROR(ABS((sheet1!$B$3:$B$81-Sheet2!C3)),""))*sheet1!$C$3:$C$81,0)-Sheet2!D3),0)) CSE

Formula calculates according to minimum difference

See attachment