xlookup

Hello, I am trying to build an XLOOKUP into my code that will lookup the first value underneath the header "STATE CD 1", as my columns can vary depending on the workbook. I am not sure if I should use the lookup in this code, or if I should make it separately so I am open to feedback on this...

I'm having an issue where the following xlookup =XLOOKUP(@A:A,PAYE!AC:AC,PAYE!F:F,"",0)&XLOOKUP(@A:A,Contract!W:W,Contract!F:F,"",0) Returns the date as a code and refuses to format into the date format below. I have manually typed the top code into a separate cell and formatted as date and...

Any ideas on how to get spilled results across and down for the following? I'm using SMALL function to find the 3 smallest values for each person. I can only get it to spill across or down - is there a way to get it to spill across and down?

This is a simplified version of what I need to do; to summarize the situation: - I have a table with many columns (only 3 are relevant for now) - Some of the rows may contain identical data in these columns (ex. the duplicate Shirt-Red-A) - I need a single cell to contain the "Brands" that have...

Hi all! I am having an issue with XLOOKUP and VLOOKUP and can't figure out if it is a formatting error with the text, specifically the 'XH' at the start of the text To my eyes, I have checked the data set both sides and the format is the same for both so the lookup's should work The formula...

Is it possible to replace all formulas containing IFERROR, INDEX and MATCH formulas with XLOOKUP? For example: this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);10);0)" to this "XLOOKUP(A112; TB!A3:A9999; TB!K3:K9999; 0)" <- (I assume this is a correct 1:1 replacement of the above)...

Dear Excel Experts, I have a below formula whereby it returns the name with the closest date from today's date. =LET(f,FILTER(I2:I18,E2:E18=R2),XLOOKUP(TODAY(),P2:P18,f,XLOOKUP(TODAY(),P2:P18,f,,-1),1)) While the formula works fine when the range is set with specific rows, when I set the...

Hi, I have the below table and what I need is below: When I choose any team from the dropdown list, I will get the top 3 highest and lowest score for the selected team, then sort the results from lowest to highest for lowest score and highest to lowest for the highest score. I put the desired...

I have an 'xlookup' formula in cell C4 that spills as expected to the required rows, but I'm having an issue with the [if not found] argument not returning the correct data. The data I want [if not found] to return is text in the same row from another spilled array starting in cell E4. (note...

Why does Excel put in xfln in front of my lookup formula occasionally and return #NAME? =_xlfn.XLOOKUP(TRUE,B1:B3000<>"",B1:B3000,,,-1) Doesn't always do it. Version 16.84 (24041420) for Mac Many thanks

Hi, I use Xlookup to get the dates for my details tab. However, I need to refine the formula to get only today and any future dates, without getting old dates or even the dates that is formatted as 1/0/1900. Below is my sample data along with the formula that I'm using. Is there any suggestion...

I am trying to evenly assign work amongst employees based on their availability and language. Please help, thank you very much in advance.

I am trying to add classifications to my leagues based on year. Some leagues change classifications on a year-to-year basis. I am trying to use XLOOKUP to look up the year and the league, and select the proper classification from a range. The problem arises when a league changes...

Hi All, My husband struggles with Excel and finds G Sheets easier to work with. So this is my current situation. I am trying to create an inventory system of sorts. Overall I will not have more than 21 items but to create the final product I might need 3 items up to say 10 items. So what I was...

Hey everyone, I have struggling with this for days now...and my team is depending on me to figure this out soon. I need help with doing some sort of lookup on data that is in different columns below a merged header cell. I need a specific cell to be chosen based on the name that I input. For...

Hi, See attached a sample job scheduling dataset. I need to lookup the bay # and return the job # which is simple enough with an xlookup. However, there will be multiple jobs with the same bay # and I need a way to be able to only pull the job number that starts after a certain date. I would...

Hi, I've been playing around with various alternative XLOOKUP formulas and INDEX/MATCH to lookup values in a table and return a match - where I want matches that starts with "HTJ" to be prioritised. In other words, if a value is found and starts with "HTJ xxxxx" then use that, if not found...

Hello guys i have a data base where i want to first find the column header, 2nd in same column find a certain date, and 3rd return correponding date from same row 1st column. So i have prepared one sample excel file Sheet one - Calc - where user can enter the class and dates Sheet two - Log...

Hi All, Really struggling to get around the below problem. Any help would be appreciated. I have 2 worksheets within my workbook and trying to create a XLOOKUP. See scenario below. =XLOOKUP(WORKSHEET2(A2), WORKSHEET1(116 COLUMNS), WORKSHEET1(COLUMN A)) Worksheet 2 (A2) = My LOOKUP value...

Is there a way to look up a name OR number in a data validation list but only let the user select the number and then have the associated number populate in the adjacent cell? My goal is for users in a file to be able to see the project numbers and/or names (some users search by names and some...