Need to vlookup by couple criterias

Hi,

I'm having kind a strange situation and headache with this one task and need some help:
In 1st excel I have this information:
Debt Value Situation
Invoice1 44460 SMS
Invoice1 44461 email
Invoice1 44462 letter
Invoice1 44486 call
Invoice2 44463 call


2nd excel:
Debt Value Situation
Invoice1 44485

I need somehow to vlookup the right one "Situation" in 2nd excel but Invoice1 has to be equal to Invoice1 from another excel and Value has to be lower than 44485 (from 2nd excel) but the highest from the 1st excel.

So, the result has to be like this:
Invoice1 44462 Letter. Situation: Letter because Invoice1=Invoice1; 44462 is lower than 44485 (from the 2nd excel) and the highest from the 1st excel.
Invoice1 44486 means that isnt correct because in the 2nd excel 44485 value is lower than 44486

As you are using Office 365 you can use new functions SORT and FILTER for instance this way:

Formula:

Please Login or Register  to view this content.

So first you filter only these elenets, where invoice is the same as in A2 and value is not larger than in B2
FILTER(Sheet1!A2:C6,(Sheet1!B2:B6<=B2)*(Sheet1!A2:A6=A2))

then you sort on column 2 descending
SORT( result of FILTER ,2,-1)
the desired result is in topmost row of sorted array, in third column, so you pick it by using INDEX
INDEX( result of SORT ,1,3)

I finally added IFERROR, to deal with situation where there is no such situation (say Invoice3 or value of 1000

The only change from post#2 is the sheet name and the bottom of the range (some extra rows added in case you add more data to 1stExcelReport)

Formula:

Please Login or Register  to view this content.

The next formula is exactly the same but the column number in index function:

Formula:

Please Login or Register  to view this content.

I'm glad to see it worked for you.

And thanks for marking thread solved and for a reputation point