I am returning to this channel after a LONG hiatus; if you enjoyed this video, please subscribe and drop a suggestion below for future videos or if you have a general MS Office question. I would appreciate the subscription immensely! Thank you for your time.

Best excel video I've ever seen

@OfficeMystic

2 жыл бұрын

Thank ye kindly!

Is xlookup available in all versions of office or just 365?

@OfficeMystic

2 жыл бұрын

Great question! It's only available in Office 365.

How would you do the same thing but only using the first 7 digits of your record number? So, to explain what I'm trying to accomplish. I have a list of BIN numbers in column B in sheet 1 and the corresponding bank number in column A of the same sheet. Example Bank # 123 (A2) Bin # 1234-56 (B2). Now to do my daily job, I import a list of cases from card company which will contain all info I need EXCEPT the bank numbers. (the card company does not know our bank numbers because they are for internal purposes) currently I spend a lot of time manually filling in bank numbers by control F in a separate source. I want to use a formula to match the first 7 characters if one cell in imported data, compare to BankBin table in sheet 2, and return the Bank number that is associated sheet 1 (the sheet with all the rest of the info)

@OfficeMystic

Жыл бұрын

If you're only interested in finding a match of the first 7 characters/digits, you can try something like the following: =XLOOKUP(LEFT(B2,7),Sheet1!B:B,Sheet1!A:A,"N/A",0,1) You're basically only pulling the first 7 characters in the "lookup value" portion of the formula.