In a previous article we covered The Basics of VLOOKUP. VLOOKUP is a very versatile function that can be combined with other functions. When you learn to combine VLOOKUP with with other functions it makes it super powerful. In this article we will look at combining VLOOKUP with TEXT functions.
Lets quickly refresh some basics of VLOOKUP
The syntax for VLOOKUP is
VLOOKUP = (lookup value, table array, column index, range lookup)
Lookup value is the value you wish to look up. This value must be in the far most left column of the table
Table array is the table in which you want to search. The first column will always be the column that contains the lookup value.
Column index is the column number you wish to return the data from.
Range lookup offers a true or false selection. Where true is an exact match. This will only return a value where an exact match to the lookup value is found. When using an Approx. match (false), items in the table must be sorted from lowest to highest, or in alphabetical order.
VLOOKUP within TEXT - The Problem
Below we can see two sets of data. The first contains the phone area code with the region and the second contains customer phone number. We want to use the phone number to carry out a lookup and return the region.
Looking at the Customer Phone Numbers, the first 4 digits represent the area code. These values are text. We can recognize they are text because numbers
Posted from my blog with SteemPress : http://theexcelclub.com/how-to-carry-out-a-vlookup-within-text-in-excel/
What text function is needed in this case? MID
What is the full formula that you used? =VLOOKUP((MID(A2;4;2)+0);$E$2:$F$7;2;FALSE)
- muftie
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
What text function is needed in this case? MID
What is the full formula that you used? =VLOOKUP(MID(A2,4,2)+0,$E$1:$F$7,2,FALSE)
That was fun. The really cool part...I've used Text Functions for years and never knew that I could turn the result into a number by adding 0. I absolutely love learning new things!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
The text function that is needed in this case is called MID
The full formula that I used is =VLOOKUP(MID(A2,4,2)+0,$E$1:$F$7,2,FALSE)
- Kenroy Hunter
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
=VLOOKUP(VALUE(MID(A2;4;2));$E$2:$F$7;2;0)
- Branislav
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Thank you Paula for this excellent tutorial. I use Vlookup very often and also tried to use LEFT, RIGHT, MID to extract certain characters using using it in Vlookup. But I wasn't successful. Now that I learnt that the information extracted is in text format, I can try out a lot of things at work.
Below are the answers for the exercise:
What text function is needed in this case?: MID
What is the full formula that you used?: VLOOKUP(MID(A2,4,2)+0,E:F,2,0)
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Excel Learn and Earn Activity 6:
What text function is needed in this case?
MID
What is the full formula that you used?
=VLOOKUP(MID(A2,4,2)+0,$E$1:$F$7,2,FALSE)
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
@theexcelclub that was an excellent post and tutorial. I 100% enjoyed carrying out the learning activity. Here are my answers
What text function is needed in this case?
I would use the MID function
What is the full formula that you used?
=vlookup(mid((a2,4,2)+0,E1:F7,2,false)
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Nice work @dernan, you are correct. thank you for taking part in our learn and earn activity
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
✅ Enjoy the vote! For more amazing content, please follow @themadcurator for a chance to receive more free votes!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :
Click here to view your Board
If you no longer want to receive notifications, reply to this comment with the word
STOPDownvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit