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

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.