Lookup Value & Return Corresponding Result
Return Value Based on Lookup Match
Got any Excel/VBA Questions? Free Excel Help
Lookup With Cell
While we can use any of the links above for lookup formulas, all require a table of cells in a Worksheet. If you only have small number of items to return based on the value of another cell, we can do the lookup without leaving the cell!
Choose & Match
The formulas we can use to perform the lookup within the cell is the CHOOSE function, nested with the MATCH function. See Excel help if unfamiliar we these functions. Ok, let's assume you have a changing value in A1 and depending on that value we wish to return a result to B1. For example, keeping it simple, we have a Validation List that a user can choose from any one of these Cat, Dog, Mouse, Horse or Rabbit. Based on their choice we wish to show a different result in B1. In B1 Enter this formula;
=CHOOSE(MATCH(A1,{"Cat","Dog","Mouse","Horse","Rabbit"},0),"Cat Food","Dog Food","Mouse Food","Horse Food","Rabbit Food")
Enter, or choose any value from Cat, Dog, Mouse, Horse or Rabbit and you will see how it works. The use of {"Cat","Dog","Mouse","Horse","Rabbit"} in the MATCH Formula is know as an array constant (See Excel help for details). Not be confused with array formulas.
Keeping it Clean & Global
The main problems with the above formula is it's long, but most importantly to edit the lookup values or the array constant, we would need to do each cell individually, or useEdit>Replace, if possible. This is where we can range names, or specifically named constants. However, when done, we can no longer use the CHOOSE Function. We use the INDEX Function instead.
1) Go Insert>Name>Define and type Pet in Names in workbook
2) In Refers to: type ={"Cat","Dog","Mouse","Horse","Rabbit"} and click Add
3) Type PetFood in Names in workbook
4) In Refers to: type ={"Cat Food","Dog Food","Mouse Food","Horse Food","Rabbit Food"} and click Add then Cancel.
Now, in the cell use
=INDEX(PetFood,MATCH(A1,Pet,0))
When/if we need to edit the named constants PetFood or Pet, we can do so in one location and our result will flow through the entire Workbook.
Lookup Scale
In the above examples we have used text values. However, it is often needed that we need to lookup numbers that match a scale. That is, all results between 0 and 99.99 should return one result, while those between 100 and 199.99 another and so on... Let's say we need to match the sales amount by person to know what percentage their commission is.
1) Go Insert>Name>Define and type Commission in Names in workbook
2) In Refers to: type ={0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1} and click Add
3) Type Sales in Names in workbook
4) In Refers to: type ={0,100,200,300,400,500,600,700,800,900,1000} and click Add then Cancel.
Now, in the cell use
=INDEX(Commission,MATCH(A1,Sales,1))
This will return a % between 0 and 100, based on the value in A1.
| Sales $ | % Commission |
| 0-99.99 | 0% |
| 100-199.99 | 10% |
| 200-299.99 | 20% |
| 300-399.99 | 30% |
| 400-499.99 | 40% |
| 500-599.99 | 50% |
| 600-699.99 | 60% |
| 700-799.99 | 70% |
| 800-899.99 | 80% |
| 900+999.99 | 90% |
| 1000+ | 100% |
It is important to note that both array constants (in the Refers to) are in Ascending order and we have use 1 for the optional Match_type argument for the MATCH Function. You can use Descending order, in which case Match_type must be -1
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
