I consider myself an advanced excel user, but I can't quite figure this one out. Keep in mind, I need to figure out how to do this without using a macro.
Here's an example of what I'm doing. The cells in the Recommendation column in Table 1 is where the formula will need to go. Basically, I need to figure out a recommendation based on the Letter and Number. Let's take row 1 in Table 1 for example. I would need to somehow vlookup the Letter cell in Table 1 in the Table 2 array, and then figure out where the number in Table 1 falls in Table 2 (terrible explanation, I know).
Basically, the recommendation in the first row of Table 1 is Pig because if you look at Table 2, and only look at the rows for Letter A, Number 3 falls between 0 and 5, so the key says we should recommend Pig. How in the world would I do this in a formula?
TABLE 1 - Recommendations
<tbody>
</tbody>
TABLE 2 - KEY
<tbody>
</tbody>
Here's an example of what I'm doing. The cells in the Recommendation column in Table 1 is where the formula will need to go. Basically, I need to figure out a recommendation based on the Letter and Number. Let's take row 1 in Table 1 for example. I would need to somehow vlookup the Letter cell in Table 1 in the Table 2 array, and then figure out where the number in Table 1 falls in Table 2 (terrible explanation, I know).
Basically, the recommendation in the first row of Table 1 is Pig because if you look at Table 2, and only look at the rows for Letter A, Number 3 falls between 0 and 5, so the key says we should recommend Pig. How in the world would I do this in a formula?
TABLE 1 - Recommendations
Letter | Number | Recommendation |
A | 3 | Pig |
A | 12 | Dog |
A | 7 | Horse |
<tbody>
</tbody>
TABLE 2 - KEY
Letter | Recommendation | Minimum Number |
A | PIG | 0 |
A | HORSE | 5 |
A | DOG | 10 |
B | CAT | 7 |
C | FISH | 3 |
<tbody>
</tbody>