Xlookup Puns

Everyone seems to swear by xlookup, and of course I’ve looked up the difference, but I’d love to hear why it’s better in the real world rather than just by the formula’s definition.

Currently preparing for my B4 internship, I don’t know how looking for my ex is going to help me… any tips?

I have a question about the XLOOKUP function in Excel. I am self-employed and I am trying to improve my tool that I use to write my invoices. For this I have an Excel file with two worksheets. One worksheet is my timesheet in which I enter the client, the date, the activity and the time spent. In the other worksheet I manage my invoices. Here I enter in particular the invoice number, the client and the billing period.

I've been trying for some time to write a formula that automatically finds the corresponding invoice number for each entry in the timesheet, if the entry can be assigned to an invoice. For this I need a LOOKUP function that returns the invoice number if the following criteria are met:

An invoice exists for the client AND the date of the respective timesheet entry is within the billing period of the invoice.

I tried this: https://exceljet.net/formula/xlookup-with-logical-criteria and put together this formula for myself:

=XLOOKUP(1;(Table4[Clients]=[@Client])*([@Date]>=Table4[Billed Period (Start)])*([@Date]<=Table4[Billed Period (End)]); Table4[Invoice Number]) 

Unfortunately that doesn't work :/

I think it may be because I am working with tables? However, this is very important for me, because without tables my automatic invoice generation in Word will stop working. Could someone help me?

Thank you very much!

Hello

I am working on an Income statement, where I'd like to make as much automation as possible (because its fun).

The issue I'm having is, I would like to sum two (or more) numbers in a cell by looking up the value that corresponds in another array, where the value occurs more than once.

In e.g.

0001: Revenue Sum revenue 0001: 500

0003: 100

0001: 300

Here I would like to use Xlookup in order to find the account and corresponding value and sum them.

I hope my question is understandable.

EDIT:
I have inserted two pictures for examples. In the second picture I have collected the adjustments that are meant to go in the adjustment column in the first picture. I would like to sum the adjustments made in account xxxx in picture 2 in the cell of adjustments column in picture 1.

I have tried the sumifs function, and tried it quickly but haven't have time to fully explore it.

https://preview.redd.it/q2671nk331a81.png?width=1748&format=png&auto=webp&s=c4e35f8d024806b4d0e4fab641128a69b6e69601

https://preview.redd.it/8wh0ut1531a81.png?width=1050&format=png&auto=webp&s=8e42e545156758123d67080160fbf8494263a39e

I am trying to use an XLOOKUP to find an exact match for a specific cell. However, one of the criteria I am trying to use “Supplier Name” is slightly different on the table I am pulling from.

Can I add one of the criteria in the lookup value as a wildcard? As in can one of the criteria be “if the return array contains this, then its a match”.

Hello!

I am a self-learner here and schedule for a hockey league. There are sometimes that there are two teams that have the same players that play in the league, and I can't schedule them on the same day.

Let's call them the Mighty Ducks 1 and Mighty Ducks 2.

In excel, I have a Home column, an Away column, then Date, Start Time, Arena.

In another table, I have a Team Preferences tab w/ a column called "Team Conflict" that lists another team that they can't conflict with. Then, in the main schedule I use an XLOOKUP to list the conflicts.

My question is, what type of formula would I be looking to use (happy to fine tune it myself but don't know where it works) to do something that essentially says:

"If Mighty Ducks 1 play = date AND Mighty Ducks are ALSO listed on that same date", value if true = error.

Do I need to use the INDEX formula? Is it just an IF statement? I can't wrap my head around it.

Again, columns I have right now are:

• Home Team
• Away Team
• Date
• Home Team Conflict (XLOOKUP)
• Away Team Conflict (XLOOKUP)

Any advice?

I have Worksheet A (screenshot included below), where the rows contain patient barcodes, and the columns contain various medications. I need to input whether a patient has (1) or has not (0) taken each medication, which is derived from Worksheet B (screenshot included below), where the same patient barcodes and their associated medications are included. Of note, a patient's barcode is listed each time they fill a prescription, even if it is the same type of medication. Thus, some patients may have their barcode listed many times. Either way, I am only seeking first-instance medication information (i.e. “has this person ever taken this medication at least once. If so, that counts as a “1” - has taken this medication).

Basically, the data connecting the two worksheets are the patient barcodes. I need a formula or strategy for: “If this patient barcode in Worksheet A is also found in Worksheet B, please return a 0 if this barcode is not associated with this specific medication, and a 1 if if it is associated at least once.” I am doing this to ultimately run logistic regression (outcome of interest column to be added).

I originally tried filtering the data for each medication I’m interested in, and then using the XLOOKUP and IF function to have it return the status of the medication of interest for each barcode, but XLOOKUP does not work on filtered data and it was only returning me the first medication that was listed for each set of patient barcodes.

https://imgur.com/a/sOzLAUY

Microsoft 365 Apps for enterprise

Version 2102 (Build 13801.21092 Click-to-Run)

Desktop Windows 10

Excel knowledge level: Beginner

I'm trying to automate a shift schedule for docents, but I'm having trouble getting all matches to show. I have a tabs for employees and their shifts, the monthly schedule, and daily schedule. Basically I would like to use the employee tab to populate the monthly data, and the monthly to be used to populate the daily.

https://preview.redd.it/een4tay5el581.png?width=1184&format=png&auto=webp&s=11fd3a342902a086aed50c8e6dcf19cef0068a2f

https://preview.redd.it/1nqsbrjtdl581.png?width=532&format=png&auto=webp&s=11a11a99275744f02ed300b16e4826f8b9ef06c9

We are the Microsoft Excel product team. Come ask us anything, from XLOOKUP to Sheet Views to lambda functions. Our whole team will be online answering your questions starting at 10 AM PT on March 10, 2021. Proof: https://twitter.com/msexcel/status/1361792480723795971

/////

That’s a wrap for today, folks! Our team will continue to work through your unanswered questions offline.

Thank YOU so much for your time and your questions & feedback!

Please fill out this short survey at https://forms.office.com/r/uyHy0Tc8yX to let us know what you think of this AMA and future AMAs.

The XLOOKUP works when I use only one array, but when I try to use 2 arrays it doesn't work, I'm using it with Logical tests (IFS), what i wanna do is designate a number but the function only is working with one array...

I usually wRite =XLOOKUP(IFS(...Tried using =XLOOKUP(XLOOKUP(IFS... but i don't know why it isn't working

Any suggestions?

i wrote the \"1555\", this is where I use only one array

here I am trying to use two arrays and it doesn't work

I am working on a (too) large dataset with 55k rows, which requires a lot of calculations and lookups.

As it starts getting slow, I wonder, if I am better of referring to my lookup tables, using a reference to the whole columns like =index(lookup!AX:BE,A2,B2) (which allows me to easily add lookup values below), or do I gain speed by using arrays that are limited in length like =index(lookup!AX1:BE60,A2,B2)?

After installing and activating Office 2021 LTSC, I tried using one of the better features implemented for Excel, XLOOKUP. However, when I tried writing =XLOOKUP in a cell, the formula was not recognized. Furthermore, looking up XLOOKUP in the formula bar did not return any results. XMATCH seems to be missing too. I downloaded 2021 ProPlus using Microsoft's CDN server, and activated with KMS ALL VL. I even tried updating to the latest version and restarted the computer afterwards.

Has anyone experienced this as well with Excel 2021? Any thoughts on why XLOOKUP is not working?

I’m dealing with approx 33k lines of data with multiple instances of the same names within columns. One column has whether a specific meeting has taken place.

I’m trying to pull through only those that have this specific meeting outstanding.

The issue I’m having is that the formula is only pulling through the FIRST instance of the meeting being outstanding, rather than every instance. Any one able to help please?

Formula I’m using (written on mobile so excuse if it changes appearance) =xlookup(“Specific Meeting”,Main!M:M,Main!A:C)

So, the formula is working, but I need a way to make it return every instance of Specific Meeting, rather than only the very first one

Hey all,

I've been trying to find an answer for this but I can't seem to dig one up anywhere, and I'm beginning to feel like there is some serious beginner stuff that I missed somewhere.

All I'm trying to do is use WorksheetFunction.Xlookup in VBA, but with multiple criteria.

In a regular sheet, the formula for multiple criteria would like this (using named ranges):

=xlookup(FirstItem & SecondItem, FirstRange & SecondRange, ReturnRange)

Whereas, a single/normal xlookup would look like this:

=xlookup(FirstItem, FirstRange, ReturnRange)

Easy as pie. Even a "normal" xlookup in vba is easy enough, as it would look something like this:

WorksheetFunction.Xlookup(FirstItem, FirstRange, ReturnRange)

Simple! ...only not so when I try to do multiple criteria. If I try this:

WorksheetFunction.Xlookup(FirstItem & SecondItem, FirstRange & SecondRange, ReturnRange)

the "&" in the VBA code combines the variables together instead of putting them into the formula as two separate values/inputs. I've tried experimenting with a series of brackets and/or """, but either that doesn't work or I'm just doing it wrong. They tend to come back with a Error 13: Type Mismatch.

And of course, I've been googling for a bit too to no luck.

How does ones put multiple criteria into this formula in VBA? My gut tells me I'm missing something REALLY simple, but who knows, maybe it's one of those things that's way more complicated than it should be.

Thanks in advanced if I don't get back to you tonight. It's getting a little late for me.

I have a table like this showing people's meetings ("Y" means there's a meeting that day):

I want to return the date of the most recent meeting they attended and the date of their next meeting on another sheet:

I know I can use the TODAY() function to get today's date, but how do I lookup the date of their last/next meeting from their name?

Thanks

I have recently submitted a task in which I used something along the lines of

=INDEX($E$11:$E$569, MATCH(A1,$F$11:$F$569,0))

instead of

=XLOOKUP(A1, $F$11:$F$569,$E$11:$E$569,0)

Because I didn’t know about XLOOKUP until about an hour after I submitted my work. Is there a big difference in efficiency/ would my use of INDEX and MATCH still be considered appropriate?

Everyone got hyped at the XLOOKUP feature (which should have been created 10 years ago), including me. However, the formula is no available on Excel 2019 and the 2021 is not yet out. I found some work-around for Microsoft devices but has anyone found a way to install it on MAC?

Some downloadable file I could drop in the formula file ?

Hey folks,

I have this table that i'm trying to extract the percentage using XLOOKUP.

The formula that i'm using is

="IRS ("&XLOOKUP($B$6;'IRS'!B:C;2;1)&")"

I already tried change the cell to percentage but it didn't help

Formula Result

What i'm looking for

Table

XLookup is the bomb.com, but always looking for something better.

I am using a growing formula via "IFS" to look at a column and choose the correct end formula to output a command line.
Ex.1

=IFS($G2="WiseNet","rtsp://zeroeyes:Zeroeyes4scps@"&E2&"/profile2/media.smp",$G2="Hikvision","rtsp://zeroeyes:Zeroeyes4scps@"&E2&"/Streaming/Channels/101",$G2="Truvision","rtsp://zeroeyes:Zeroeyes4scps@"&E2&"/Streaming/Channels/101",$G2="Avigilon","", $G2="","")

I am getting more types of input for my $G2 column, which will cause my formula to grow very large. I then thought about using the Xlookup formula to look up the correct formula based on the input for $g2.
Ex.2

Formula
=XLOOKUP(G2,Verification!C2:C15,Verification!D2:D15,"")

Result
"rtsp://zeroeyes:Zeroeyes4scps@"&E2&"/profile2/media.smp"

The result is simply the text of what is in the table and not an actual formula, hence the E2 is not substituted with the value from cell E2

Lookup tables

https://preview.redd.it/xjkaui9xggr71.png?width=674&format=png&auto=webp&s=7213e5249be75f9c743289565a246c9eb4434bad

I have also tried adding a "=" either in the formula in the lookup table or in the formula itself. The = when included in the result in the lookup table, the "&E2&" disappears from the formula as it references the Verification! tab and not the tab the formula is on. This formula may be on one of 10 or so tabs fyi

Thoughts on how I can do this? May be Xlookup is not the best option.

Many thanks in advance

I’ve been gradually refactoring my files to use XLOOKUP - mostly instead of INDEX & MATCH - but today I realized I could implement it somewhere new.

I maintain an annual schedule of farmers who will deliver products to processing plants each week, working on a rolling 4-week basis. Up to 4 farmers may share a truck, and I have to report to the plants which trucking company each farmer or group of farmers will use. Most of the time farmers use the same trucker, so my previous formula concatenated the farmers’ names and then used two LOOKUP functions to identify the most recent truck for identical loads (I use a naming convention and always enter the names in alphabetical order); today, however, I stuck an XLOOKUP in there and used -1 in the last section to have it search in reverse! It’s more straightforward than LOOKUP, and I like that I was able to eliminate two instances of a volatile function.

Edit: Thank you for the silver, kind people. I’m glad we were able to share some joy.

Hi all,

I no longer use vlookup. I only use xlookup. I see a lot of other people use index match. Which one should I be using and why?

So in my attached screenshot, how would I use XLOOKUP to return data from columns B and D but not column C?

https://imgur.com/wrUQ7xM.jpg