Lookup in mutiple cells with 3 sets of variables

[puha muncher]

Hello,

I am trying to figure out three similar formulas that will use lookup, match and/or index and return a single cell with three variables (refer attached excel file for actual data).

Example

1. Input Variables

Variable 1 = A
Variable 2 = X
Variable 3 = 635

2. Data Table

Column 1 Column 2 Column 3 Column 4
A X 100 01-A-1018-X
A Y 110 01-A-1018-Y
A X 117 02-A-1218-X
A Y 162 02-A-1218-Y
AF X 252 02-AF-1218-X
AF Y 336 02-AF-1218-Y
A X 349 03-A-1218-X
A Y 353 03-A-1218-Y
AF X 409 03-AF-1518-X
AF Y 505 03-AF-1518-Y
A X 531 04-A-1518-X
A Y 545 04-A-1518-Y
AF X 557 04-AF-1818-X
AF Y 643 04-AF-1818-Y
A X 680 05-A-1818-X
A Y 705 05-A-1818-Y
AF X 763 05-AF-1818-X
AF Y 775 05-AF-1818-Y
BF X 847 05-BF-301815-X
BF Y 866 05-BF-301815-Y
A X 963 06-A-1818-X
A Y 988 06-A-1818-Y
AF X 1085 06-AF-2018-X
AF Y 1180 06-AF-2018-Y
BF X 1185 06-BF-301815-X
BF Y 1258 06-BF-301815-Y
A X 1266 07-A-1818-X
A Y 1352 07-A-1818-Y
AF X 1376 07-AF-2018-X
AF Y 1432 07-AF-2018-Y
BF X 1501 07-BF-301815-X
BF Y 1585 07-BF-301815-Y
A X 1625 08-A-2018-X
A Y 1682 08-A-2018-Y
AF X 1721 08-AF-2417-X
AF Y 1796 08-AF-2417-Y
B X 1855 08-B-301815-X

3. Required Formula

Formula 1 to lookup input variables in the data table and return the corresponding value in variable 4 (with variable1 equal to variable1, variable2 equal to variable2 & variable3 equal to next lowest value in column 3). eg returns variable 4 = 04-A-1518-X (with variable 1= A, variable 2=X & variable 3=531)

Formula 2 to lookup input variables in the data table and return the corresponding value in variable 4 (with variable1 equal to variable1, variable2 equal to variable2 & variable3 equal to or variable 3 or next highest value in column 3). eg returns variable 4 = 05-A-1818-X (with variable 1= A, variable 2=X & variable 3=680)

Formula 3 to lookup input variables in the data table and return the corresponding value in variable 4 (with variable1 equal to variable1, variable2 equal to variable2 & variable3 equal to next highest value above value used in formula 2 for variable 3). eg returns variable 4 = 06-A-1818-X (with variable 1= A, variable 2=X & variable 3=963)

4. Results

Next Lowest= 04-A-1518-X
Recomended= 05-A-1818-X
Next Highest= 06-A-1818-X


Any help would be greatly appreciated.

Puha

 

[Damon Ostrander]

Hi Puha,

There are lots of ways to do this, but one that is fairly easy and doesn't require VBA is to insert a new column C between the existing columns B and C and use this formula in C1:

=IF(LEN(A1)=2,A1," "&A1)&B1&TEXT(C1,"00##")

and autofill down. This will create a lookup string that looks like:

" AX0531"

except without the quotes. You can then use the same formula as above to concatenate variable1, 2 and 3 into a similar string that will match " AX0531" if variable1 = A, variable 2 is X, and variable 3 is 531. Because of the order of concatenation, the rules for alphabetic ordering will apply for determining which of two values is larger, so it is important to concatenate the 3 variables in the order you would like for them to take precedence when there is no value in the lookup table that is exactly the same as the search string.

Damon