Vlookup with multiple criteria?

[tkbuc]

Hi everyone,

I am trying to perform a vlookup based on multiple criteria.

In the attached spreadsheet, my data is represented in columns A:E. In columns H-N, I am trying to lookup up the "Rate" for all 4 items based on the "BT", "Code" and "Key" matching. However, I also want one of the criteria's to be if the BT Term Date = the Rate_Limit_Date OR, if they don't equal, I want to return the "Rate" value that was active at the time of the BT Term Date. Does that make sense?

For example, for the first item, there are multiple line items with a code of 3J03, so I would want to return the "Rate" of the line item where the BT matches "TI", Code matches "3J03", Key matches "FR" and where the Rate_Limit_Date is less than and as close to 12/31/2013 as possible.

Any suggestions?

[FDibbins]

Will there always only be 1 match for what you want?

[FDibbins]

This is what I have so far, but it does not take into account non-exact date matches...
=INDEX($C$2:$C$204,MATCH(H2&J2&L2&I2,INDEX($A$2:$A$204&$B$2:$B$204&$D$2:$D$204&$E$2:$E$204,0),0))

[FDibbins]

OK, I just noticed that your dates in E are not real dates, they are text - you need to fix that 1st.
1. highlight that range
2. on the Data tab, click Text2Columns
3. click Next/Next/ check Date and select your data format
4. Click OK

On the assumption that there will only ever be 1 match, see if this will do what you want...
=SUMIFS($C$2:$C$204, $A$2:$A$204,H2, $B$2:$B$204,J2, $D$2:$D$204,L2,$E$2:$E$204,"<="&I2)

[tkbuc]

Thanks so much FD! Yes, there will always be only 1 match because if it came down to only the dates, I would want the one closest to the BT Term Date! I will try and use that formula tomorrow when I am back in the office and let you know how it works. I appreciate your time and expertise!

[FDibbins]

If there will only be 1 match, then my 2nd formula (SUMIFS) should give you what you want.

let me know how you make out?

[tkbuc]

Hi FD. The formula is summing the values. I actually need it to tell me the particular rate on the date the BT terminated. For example, for the first line item, what was the particular rate for BT "TI", Code "3j03" and Key "FR" on 12/31/2013.

I would expect the formula to return $37.29 because that was the rate that falls between the 8/31/13 Rate Limit Date and the 12/31/2014 Rate Limit Date (Column E). So that was the rate that was active at the time of 12/31/2013.

For the second line item, because there happens to be a Rate Limit Date = the BT Term Date of 12/31/2099, I would expect the formula to return $40.83.

[JohnTopley]

Add helper column in F

=A2&B2&D2&E2
and copy down

Custom Sort by column F (A to Z)

To get rate

=INDEX($C$2:$C$1000,MATCH(H2&J2&L2&I2,$F$2:$F$1000,1))

NOTE: dates must be dates not text.