Another Excel question

So Xlookup is new. Cool. Never heard of it until today. Tried it now and it works. Thanks for the headsup.

How often does MS introduce newer formulas and where is the best place to keep tab of these new formulas and changes?

Also, any other recently introduced new/replacement formulas?
 
This is true, as well as the dreaded #N/A when doing lookups using text values, there could have been slight data manipulation done on the one sheet e.g. padding removed, trailing spaces etc.

iserror helps
Use it in a vlookup (or xlookup)

 
OK, it seems the columns that i thought had identical data does not. there are slight changes... will ask the person who did the filtering on their side to redo, and include the data i need

thanks alot for the helpful info....

think its time to call it a day, and head over to my local Dros
 
So Xlookup is new. Cool. Never heard of it until today. Tried it now and it works. Thanks for the headsup.

How often does MS introduce newer formulas and where is the best place to keep tab of these new formulas and changes?

Also, any other recently introduced new/replacement formulas?
I can suggest another great tool I use often, it's an add-on, called "Fuzzy Lookup"

You create 2 tables, and do a lookup using 2 common fields between them, it uses something like the Levenshtein Algorithm to return the best match and give you an accuracy - you still have to peruse for logical errors, but it works very well when doing a lookup between vaguely similar text fields.

The theory - https://en.wikipedia.org/wiki/Levenshtein_distance

The add-on - https://www.microsoft.com/en-us/download/details.aspx?id=15011
 
OK, it seems the columns that i thought had identical data does not. there are slight changes... will ask the person who did the filtering on their side to redo, and include the data i need

thanks alot for the helpful info....

think its time to call it a day, and head over to my local Dros
OMG where do you work? Dros at 10am?
 
OK, it seems the columns that i thought had identical data does not. there are slight changes... will ask the person who did the filtering on their side to redo, and include the data i need

thanks alot for the helpful info....

think its time to call it a day, and head over to my local Dros
It's usually something like trailing spaces which happens when copying data from a csv file etc.

The trim function gets rid of those.

 
So Xlookup is new. Cool. Never heard of it until today. Tried it now and it works. Thanks for the headsup.

How often does MS introduce newer formulas and where is the best place to keep tab of these new formulas and changes?

Also, any other recently introduced new/replacement formulas?


if you have linkedin, then activate your 30 day free linked in learn courses.

Search for this one. Takes about 45minutes.
Excel Formulas and Functions Quick Tips


Otherwise youtube
 
The sheet with the values you are looking up, that column A needs to have unique values otherwise vlookup will return the first match or nearest match depending on your parameters.

To see if your column is unique try

And vlookup is ancient.

use xlookup
I prefer using a combined Index and Offset function. I think it gives you more flexibility.
 
OK, it seems the columns that i thought had identical data does not. there are slight changes... will ask the person who did the filtering on their side to redo, and include the data i need

thanks alot for the helpful info....

think its time to call it a day, and head over to my local Dros
Highlight the column, click conditional formatting, highlight duplicate data.

The filter your column by colour.

If it's a few that appears as duplicates you can sort them out manually. Do the xlookup for the rest.

Do you need the duplicate entries?

If not, highlight the sheet, click data and click delete duplicates.

Is there any other uniquely dentifyable info in your sheet you can use to search by? You can also use the concat feature to combine cells to make them even more unique, which makes xlookup work better

(paraphrasing here, currently on a train without a laptop in front of me)
 
Vlookup likes the following

Data in both sheets sorted (I heard this is no longer necessary, I still sort)

The lookup formula must have $

=vlookup($A3,Sheet2!$A$3:$Z$100,5,False)

This make sure when you copy the formula down, it does not move.

On the sheet where you have the information, insert numbers above the columns from 1 to 10etc, that way you know what columns to reference, instead of counting it everytime.

My 5cents.
 
It's usually something like trailing spaces which happens when copying data from a csv file etc.

The trim function gets rid of those.

iserror helps
Use it in a vlookup (or xlookup)

Use =IfNA((formula),Whatever you want to happen if it finds the error)
"" would mean it does nothing - it shows a blank cell
And they are right - trim should get rid of what you are looking for. But you can also download a little adding - will get the name for you.

Actually they have removed it. I used Power Query for most things these days.

You can specify how close your match should be. Much like the tool used to do. Blame age. I have it loaded on the other machine - but that is not with me right now.

https://appsource.microsoft.com/en-us/product/office/wa200004933?tab=overview
They never used to charge for it
 
Last edited:
Top
Sign up to the MyBroadband newsletter