Can you use VLOOKUP to Return a Formula Instead of a Value?

In order to ascertain whether or not your IF statements can be replaced it would help if you can post a sample file with examples of what is required.

A 50 statement formula sounds horrendous!

The formula being returned is actually a text string that looks like a formula. You would need to EVALUATE that string as if it were a formula (assuming you could change the row to reflect the one that the formula is on), but you can only do that using a UDF, as there is no EVALUATE function within standard Excel functions (it is available in VBA). If you are going to need a UDF, then you might as well incorporate all the functions of the long formula within it.

I suggest you post this massive nested-IF formula and explain what it is trying to do, then we might be able to suggest a suitable alternative.

Hope this helps.

Pete

Hi,

Whoa, you guys are quick! Many thanks, have edited and attached a sample of the formulas. The formula itself is fairly long and when you nest it it becomes massive..

Thanks,

No IF statement present: only one SUMPRODUCT. Which column/Cell?

Hi,

I have attached another version of the example workbook. The "Data" worksheet contains examples of the incident duration I calculate. For ease of example I have cut down the IF statement - the IF statement is performed in col F and is based on the criteria in col B - "Hours of Operation", depending on what the site hours are the SUMPRODUCT will start and stop the timer etc.

The IF formula as it is works fine, the issue is when the customer decides to change the hours of operation and I have to rewrite the long nested formula to accommodate.

The "Formulas" worksheet is my attempt to build a look up table where I can add maintain all the different hours of operation with the relevent formula calculation - col F and, if possible, perform a VLOOKUP against Col F using the "Hours of Operation" from col B in the data worksheet as the lookup value.

The problem arises as the formulas in col F are relative to its position from the data table the formulas are pulled from and not where it returns the result in the "data" worksheet.

Sorry, I hope that is a bit clearer.

Thanks,

Hi ProtonLeah,

Many thanks for your help. I'm not sure I followed your instructions correctly but I edited the existing VB script to read as per your above code. I changed the Excel settings to use R1C1 reference style and then performed the VLOOKUP using the hours of operation in the "Data" sheet Col 2 as the look up value, returning the formula from the "Formulas" sheet col 7 - Formula Text. This populates the col 7 VLOOKUP Attempt in the "Data" sheet with the formula, however, I seem to progress no further. I have copied and pasted the formulas from col 7 but it retains the formula only and not the value. I have attached my workings in the "Hours of Operation with IF Statement_v2" workbook. Any idea on where I may be going wrong?

Thanks,

Thanks ProtonLeah,

When I open the attachment, the formula calculation in Col F (col 6) returns #VALUE error for the sites other than those with coverage hour “N-S (00:00 - 23:59)”. I assumed as I was using Excel 2010 the formula “NETWORKDAYS.INTL” would calculate and the VLOOKUP would update the cell, however, the cells remains as #VALUE.

In the scenario I used your workbook as the master to perform my daily calculations and imported my raw data into columns 1 to 4, should the result in col 6 automatically update or are there some additional steps I’m required to perform?

I played around with the start and end times for sites with hours of operation “N-S (00:00 - 23:59)” and changed the times to see if the result would change in col 6, col 6 didn’t update and I figure I’m doing something wrong or am missing step.

Any ideas?

Thanks for all your help

Hi ProtonLeah, no problem, thank you for assistance, appreciate it.