Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
If you've done lookups in Power Query to pull values from one table into another, you may have used a query merge to do this. Mynda has written previously on how to do an Exact Match in Power Query and an Approximate Match in Power Query Here I'll be showing you how to use List …
A few weeks ago David T asked me to help him understand a VLOOKUP formula in a workbook he’d inherited from a colleague who had left his company. It was a VLOOKUP formula like nothing I’d ever seen before so I thought I’d share it with you. Drum roll….. =VLOOKUP(E2&F2,IF({1,0},First_Name&Last_Name,Grade),2,FALSE) David’s question was ‘what’s the …
This week I had a question from Diedre asking if she can use VLOOKUP to check multiple sheets…. 17 different sheets in fact. The idea being that if VLOOKUP doesn’t find a match on the first sheet, it will check the next sheet and so on. The good news is we can, the bad news …
This Excel Factor tip was sent in by Bob Cooke of Lincolnshire, England. Words by Mynda Treacy Last week Bob emailed me with an example of how he uses the INDEX, SMALL, IF and ROW functions to lookup a list and return multiple matches like this: It’s good timing as I actually had this …
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee. Words by Bryon Smedley. Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we can email you our Excel newsletter. Please enter a valid email address. Download the Excel Workbook and …
I had an email from Bobcat today asking how to lookup data that is spread across multiple columns. Table 1 has data with the name in just one column: And Table 2 has the name across 3 columns (D, E and F): We want to find the Employee number from Table 1 and put …
For some of us we use the VLOOKUP function all the time and for the most part is does exactly what we want, but what if you want to lookup multiple columns? Taking the example below; in cell B3 I have a data validation list that allows me to choose the player I want to …
Wildcards *?~....what am I talking about? And no, it's not an expletive. Let’s start with an example. COUNTIF using Wildcards What say you wanted to count the number of cells containing the word ‘apple’ in this table. You could simply use a wildcard (an asterisk, *, is a wildcard in Excel) in your COUNTIF formula …
If you ever work with large tables of data and you want to insert a VLOOKUP formula that dynamically updates to the next column as you copy it across, then the VLOOKUP with the COLUMNS function is what you need. That is; the col_index_num part of the VLOOKUP function dynamically updates as you copy it …
A little while ago I showed you how to do a lookup to the left using the INDEX and MATCH functions. In this Excel tutorial I’m going to show you how you can do a lookup to the left with a VLOOKUP formula together with the CHOOSE function as an alternative. Let’s look at our …
The Excel HLOOKUP formula is just like the VLOOKUP formula, only the table you are looking up is laid out in a horizontal list, rather than vertically. Hence the ‘H’ for horizontal, and ‘V’ for vertical. Just like the VLOOKUP, the HLOOKUP function can be used to find an exact match or used in a sorted …
In my previous Excel VLOOKUP formula tutorial I mentioned that there are two ways you can use a VLOOKUP but most people know one way or the other, and only a few know both. As promised here’s the second way to use it, and I call it the Sorted List version as it relies on …
The Excel VLOOKUP formula is my favourite! Perhaps because it was one of the first formulas I mastered. It gave me an insight into the power of Excel and how it could help me in my job. Interestingly, there are two ways you can use the VLOOKUP function; exact match and approximate match. However, I …
