You are here:

Excel/using lookup with a table of formulas

Advertisement


Question
QUESTION: Tom,
Its been a long, long time since I came across one of your posts. I'm guessing 12-15 years.  Don't know how I have missed them, as I used to see them all the time.

Anyway, here's the question.  Is there a formula I can use to reference a table of formulas vs building one hell of a nested if.  I have tried indirect without success.  I prefer formulas over vba, please.

The details.
I download my bank statement and need to, based on the first 5 or whatever characters, interact with the data differently.

So, for example.

From download

A1: CHECK #10683.................Formula in B1
A2: AMERICAN EXPRESS 4601048973...Formula in B2
A3: BANKCARD-SANDY SPRIN1288......Formula in B3
etc

I need to use about 10 different formulas based on the first 5 chars.

So "Check" would return formula right(A1,5) and results would 10683
and "AMERI" would return formula left(a2, 8) or 01048973
and "BANKC" would return formula left(a3, 9) or SPRIN1288

I am cleaning the data based on my criteria so I can work with other tables.

Thanks,
Mike

ANSWER: Mike,

as an example

=Match(Left(A1,5),{"Check","AMERI","BANKC"},0)

would return which entry in the list/Array, {"Check","AMERI","BANKC"} is found in cell A1 using the left five characters.

This could feed the choose function which takes an integer as the first argument and then the remainder of its arguments are expressions it will execute based on the first argument. the integer specifies which of the following argument to execute starting with 1.  


so in your example

=Choose(Match(Left(A1,5),{"Check","AMERI","BANKC"},1),Right(A1,5),Left(A1,8),Left(a1,9))

Now I assumed you wanted to process A1 in different ways based on the value in A1.  

However you cited A1, A2 and A3 in your example.  If that is infact the case, then you could adjust the formulas in argument 2, 3, and 4 to reflect whatever formula you need to execute based on what is in cell A1.  

You would just expand the choose function to handle 10 conditions.  Also, it the entry being checked could fail to match a value in you list you would need to use an iferror function to handle the error if that is appropriate.

--
Regards,
Tom Ogilvy




---------- FOLLOW-UP ----------

QUESTION: Thanks Tom.  The choose function is a definitely a better option to me from a readability standpoint and with only 10 choices, will suffice nicely.

BUT, suppose I had 150 variables, a formula in itself would be very unruly.  

So that being said, without VBA, would it be possible to build a table of formulas and return and evaluate the formula?  Is it even possible to return a formula and evaluate it.

Since you provided a solution, just point me to other places where I might research this.

Thanks Again,
Mike

Answer
Mike,

This appears to be a duplicate question which I answered back on 21 Aug.  Here is the answer I gave then:

Mike,

Microsoft has begun slipstreaming in improvements to excel so I can't say I am up to date on the latest functions they have added - but there never has been a function that would do what you describe.  Indirect is designed to convert a string into an actual cell reference.  It isn't designed to take a string and evaluate it as a formula.  

Laurant Longre had an addin (morefunc) that had an evaluate function that could do what you describe.  But he has dropped out of sight many years ago although you might be able to locate a copy of his addin  (also, I don't know what compatibility with current versions of excel  his addin has).  

So sorry, but my basic answer is NO there is no built in support for this.  

that said, I have put in some URLs that describe using the Excel 4 Macro function Evaluate.  
If you absolutely had to have this capability, you might want to pursue this method
http://www.vertex42.com/ExcelArticles/evaluate-function.html
http://www.myonlinetraininghub.com/excel-factor-12-secret-evaluate-function

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.