New function in Excel 2016 to display in a cell, the formula contained in another cell

New function in Excel 2016 to display in a cell, the formula contained in another cell

You must have come across a number of situations when you were trying to explain a formula that you have written in Excel and the result of the formula. This kind of situation basically demands both the formula expression and the formula result to be displayed simultaneously. For example, from the below screenshot, if you are trying to explain that the formula in cell G2 is =TODAY() and that this is a built-in function in Excel that returns today’s date, you might instruct to view the result in the cell (active cell in the below screenshot) and to view the formula contained in the active cell from the formula bar (highlighted in rounded box in below screenshot).

 

image

 

what if I you want to see both formula and the result of the formula side by side or one below the other and avoid viewing two extreme corners of the window? Excel offers a new function named FORMULATEXT to address this purpose. FORMULATEXT function takes only mandatory argument referring the address of the cell containing formula that you wish to view. It returns the formula contained in the cell whose address is passed as the only argument to it. In our example, if you enter the formula =FORMULATEXT(B2) in cell F2, it returns the value as =TODAY() as in the below screenshot. Now the formula used in cell G2 and the value returned by it are just side by side making it easier to understand the formula and explain it to others too.

 

image

 

A nifty handy formula for people like me Smile Happy Excel Learning..!!

Advertisements

VLOOKUP: Right to Left Lookup

We all now know that VLOOKUP has one major constraint that the column in the lookup table from where the lookup value is to be searched should be the first column in the lookup table.

Here is a tricky way that you can use to overcome this constraint. The trick is, if we can build the table_array argument (i.e., the lookup table) of the function at runtime in a way ensuring that the first column is the column in the lookup table that is to be searched for the lookup value, the problem is resolved. This is achieved using CHOOSE function inside VLOOKUP in the place of table_array argument to build the lookup table in desired manner at runtime.

Example: Lets understand this with an example. Below are the customer names and their respective phone numbers. you are given a customer name and asked to lookup the customer name from this table and return the phone number of the respective customer.

Challenge: However, the customer name column in the lookup table from which the lookup value will be searched is not in the first position. see the screenshot below:

VLookup_3_1

Solution: Here is the VLOOKUP function with CHOOSE function in place of lookup table to return the right values entered in cell F36 to lookup the value in cell F35 and search it in the range B36:C36 (obviously not in the first column) and return the phone number. see the below screenshot:

=VLOOKUP(F35,CHOOSE({1,2}, C36:C46, B36:B46), 2,FALSE)

VLookup_3_2

Explanation:

First argument to the function:  WHAT to search/Lookup: cell F35 has the value to be looked up.

2nd argument to the function – WHERE to search/lookup: Here is the CHOOISE function is used as follows: CHOOSE({1,2}, C36:C46, B36:B46). Here the first argument {1,2} will arrange the arrays referred in 2nd and 3rd parameters in that order (i.e., the array referred in 3rd argument of CHOOSE function – C36:C46 comes first and then comes the array referred in 4th argument of CHOOSE function – B36:B46) and returns it as a range/lookup table. so when this happens, the returned lookup table will have C46:C36 as first column that has customer names where the lookup value from cell F35 will be searched, and B36:B46 as second column that has the phone number to be returned.

3rd argument to the function – WHICH column in the lookup table, returned at runtime, enumerating from left to write (corresponding to the row where the lookup value is found) has the value to be returned ? Here phone number to be returned is in column in the 2nd position (i.e., B36:B46) in the lookup table returned at runtime.

4th argument to the function – How to search. Here we only want to perform exact search. So, argument passed here is 0(zero) or FALSE.

Testing:

Now in the screenshot above, we already have a customer name in the cell F35 – “rathod”, so the value returned should be: 7174683825. When you hit enter “after” key after entering the formula in cell F36, you would see the correct value being returned as in the below screenshot.

VLookup_3_3

Hope you enjoyed reading this post..!!

Want to learn more VLOOKUP tricks..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right. Happy Learning.. 🙂

Do leave your comments, if any..

MS Excel Keyboard Shortcuts

Hi.. Here I have composed a comprehensive listing of keyboard shortcuts for Microsoft Office Excel 2010 in downloadable PDF format.

Please do share the keyboard shortcuts if you know any that are missing in this list and help improve this listing.

Do share your comments or suggestions, if any..

Happy time until my next blog post.. 🙂