Excel VLookup - Text Key Exact Match Numerical Value Not Exact Match

0
votes

Trying to solve a problem in Excel without programming in VBA to solve the following example:

I have 2 or 3 Text Values which I am concatenating to use for a multiple constraint Vlookup/Index Match. The Vlookup then needs to reference a numerical field (Term) as a range match (Vlookup True) to return a single value. The following matrix has a small sample of the Matrix and "Key" Column used.

Given the following: Grid=201902_CH_C15 and AmortizationType=Fixed and Term=180

I expect a value of 0

Given the following: Grid=201902_CH_C15 and AmortizationType=Fixed and Term=120

I expect a value of -28.5

Given the following: Grid=201902_CH_C30 and AmortizationType=Fixed and Term=300

I expect a value of -5.

enter image description here

1
excelvlookup
what are the current incorrect values you've been getting instead? (Also, shouldn't the key also have "Term" appended to it, since you're using that as part of the lookup values?) – Mistella
So I haven't been able to figure out which I should be using to even begin the problem as this in not a direct match on all 3 values, I would think the 2 Text Constraints form an initial key to match and create a smaller subset array where the 2 Text Constraints Match and the Term Column still varies. From this Term Column the next match would then isolate and record the value. – Saket Nigam
You could also try something like this exceljet.net/formula/index-and-match-with-multiple-criteria – Mistella
This Worked - I've used the Array solution for the testing true false before, but never thought that you could use it for testing <= inside the nested match function: =INDEX(Value,MATCH(1,(Grid=GridArray)*(AmortizationType=AmortizationArray)*(Term<=TermArray),0),0) – Saket Nigam

1 Answers

0
votes

Thanks to Mistrella - The following works using the Array Formula by doing Control+Shift+Enter at the End

=INDEX(Value,MATCH(1,(Grid=GridArray)(AmortizationType=AmortizationArray)(Term<=TermArray),0),0)