<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Lookup Value & Return Corresponding Result

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Return Value Based on Lookup Match

Got any Excel Questions? FreeExcel 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 use Edit>Replace, if possible. This is where we canRange Names , or specificallyNamed 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 orPet, 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 whatpercentage their commission is.

1) Go Insert>Name>Define and type Commission inNames 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 optionalMatch_type argument for the MATCH Function. You can use Descending order, in which case Match_type must be -1

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

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