Vlookup with Closet match from other column

[santhoshkunder]

Hi All,

I have come across a situation where i should be able to do a lookup which can give me the results when it doesn't find a value in particular column by taking a closest match from the same column.

For example I have 3 columns Year, month, salary respectively. I am trying to get the value from salary column by looking up year and month in a concatenated mode. Say for example if i don't get the value for Year 2015 month Jan then my lookup should find for the closest value i;e Year 2014 and Month Jan data. It goes on like this.

Thanks
santhosha

 

[Khalid NGO]

Hi Santhosha,

Try this with Ctrl+Shift+Enter:

=IFERROR(INDEX($C$2:$C$11,MATCH(DATE(G$10,G$11,1),DATE($A$2:$A$11,$B$2:$B$11,1),0),),INDEX($C$2:$C$11,MATCH(MIN(IF($B$2:$B$11=G$11,IF($A$2:$A$11<>G$10,$A$2:$A$11))),$A$2:$A$11,0),))

Regards,


Edit with IFERROR.

 

[santhoshkunder]

Thank you for the reply. It works.Do we have any Array free solution since array formals are not supported in Xcelcisus(An SAP dashboard making tool)

 

[Khalid NGO]

Thank you for the reply. It works.Do we have any Array free solution since array formals are not supported in Xcelcisus(An SAP dashboard making tool)

Didn't found non-array INDEX solution, can you check this:

=IF(SUMPRODUCT(($A$2:$A$11=G$10)*($B$2:$B$11=G$11)*($C$2:$C$11))>0,SUMPRODUCT(($A$2:$A$11=G$10)*($B$2:$B$11=G$11)*($C$2:$C$11)),SUMPRODUCT(($C$2:$C$11)*($B$2:$B$11=G$11)*($A$2:$A$11<>G$10)))

Not sure this meets your requirement, since SUMPRODUCT is array formula.
Regards,