[Affiliate]
Blog Stats
- 3,028,937 hits
-
Join 3,888 other subscribers
- Follow wmfexcel on WordPress.com
Search wmfexcel
Want Google to translate this page?
-
Recent Posts
Top Posts & Pages
- How to sort bar chart in descending order?
- Show number in thousand (k) or in million (M) by using custom format
- How to replace value with a value from another column in Power Query
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- Insert a text string into a specific position of another text string in #Excel
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- Rename column names in a dynamic way with #Excel #PowerQuery
- Send a cell content to Google search
- Open an embedded Excel file during a slide show in PowerPoint?
- Perform VLOOKUP with 2 lookup values
My YouTube Channel
My Facebook page
- My Tweets
Tag Archives: VLOOKUP
Fixing inconsistent data type in lookup table in #Excel
A common VLOOKUP problem with an easy fix The situation – VLOOKUP fails… Have you ever encountered something like this? This is quite common. We are sure that the VLOOKUP formula is correct. We are sure that the lookup values … Continue reading
Be cautious when using XLOOKUP
Can you spot the error in the above formula? It’s not about the new function of XLOOKUP. It’s about inevitable human error… If you are an Excel fan, you should be aware of the exciting XLOOKUP function in Office 365. … Continue reading
Hard-coded range expands automatically with #Excel Table
The INDIRECT trick of using Structured Reference in Conditional Formatting Is it a feature or a bug? This is an extension of the previous post, in which we discussed the use of INDIRECT trick to deal with Excel Table in Conditional … Continue reading
Learn something new from something that I think I knew well…
Today is a public holiday in Hong Kong. A perfect break in the middle of a busy week. What did I do on a public holiday? Excel, Excel and Excel. 🙂 I watched a few videos from my favourite YouTube … Continue reading
#PowerQuery makes advanced VLOOKUP so easy!
Do you use VLOOKUP? If you do, you should know there are limitations to VLOOKUP. Also, there are some cases VLOOKUP is not straightforward and efficient. Consider the following cases: What we want to achieve is to map the corresponding … Continue reading
Lookup a specific combination of characters exclusively, e.g. 5A not 5AA
Got the following question: hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only … Continue reading
Three different ways to do case-sensitive lookup
If you do VLOOKUP, you probably know that one of the limitations of VLOOKUP is case-insensitive. It means case is not a consideration in the lookup process, where PETER is essentially the same as peter. Hence VLOOKUP will consider that … Continue reading
VLOOKUP the Nth matched item, with helper column
This should be the last post in 2015. So it’s better to answer unanswered question in the year. 🙂 Question: “VLOOKUP will return the first matched value found, in case there are multiple matched records what formula should we use?” This … Continue reading
Nested IF vs. VLOOKUP – Which one to use?
A well-drawn flowchart or a well-organized table helps you write a successful formula effectively Continue reading
Pay attention when you concatenate – A1 & B1 vs. A1 & “|” & B1
In many case, we may want to combine texts from different cells. This can be done by either CONCATENATE function or simply an Ampersand &. E.g. A1 & B1 & C1 …… We may take it for granted as it looks so … Continue reading
3D VLOOKUP – Perform VLOOKUP from more than one table
Pls have your 3D glasses ready… only if you are going to watch a 3D movie. You don’t need it for a 3D VLOOKUP. We talked about how to do a 2D VLOOKUP by using MATCH together with VLOOKUP. With that … Continue reading
Alternative to vlookup – Index and Match
Lookup from Right to Left In the above example, if it is given the Student ID, it would be so easy to find one’s corresponding Name and Grade by using vlookup. However, if we would need to find out the … Continue reading
Advanced vlookup – wildcard characters “?” and “*”
Why vlookup gives me wrong answer??? Bugs in vlookup??? I am sure it’s not about text vs. number, but it still fails to give the correct answer!!! I guess many users are not aware of the wildcard characters in Excel. … Continue reading
Advanced vlookup – Text vs. Number
Why does vlookup not return the lookup value? I am sure it is in the source data table. I see it by my eyes. Have you ever experienced this?? That’s normal when you import your data from other database. Fortunately … Continue reading
vlookup – True or False??
Usually we use vlookup for answering a particular question like “How many customers we had on January 1st 2014?” We expect an exact match and hence using FALSE as the last argument in the vlookup formula. When do we use … Continue reading
vlookup with Match
Hard-coding the col_index_num in the vlookup formula is not a wise choice as you might have to look into the formula and make changes every time you copy and paste your formula. An alternative approach of marking column index on … Continue reading
Tips in constructing vlookup
Tips in constructing vlookup We talked about the syntax of vlookup and we had written a vlookup formula successfully in the previous post. YEAH! Now we want to ride on the formula by copying it DOWN and to the RIGHT … Continue reading
The basic of vlookup
vlookup – Something you should know about The basic of vlookup Definition of LOOKUP (http://www.merriam-webster.com/dictionary/lookup) : the process or an instance of looking something up;especially: the process of matching by computer the words of a text with material stored in memory v, in the … Continue reading
wmfexcel 