xlookup

Hello, I am wanting to spill an xlookup of a maximum number but am hitting a #VALUE error. The basic format is: week cat rat intended result basic intent 2 34 16 cat =xlookup(max(b2:c7),$b$2:$c$7,$b$1:$c$1)) 3 48 24 cat 4 37 15 cat 5 25 40 rat 6 54 45 cat 7 38 39...

Hi all, I currently have a list that looks like this (albeit much much much larger in reality): I would like to create a slicer for column D (Height (CM)). However, my table is not a pivot table. Does anybody know how to do this? I read online that I have to go into the design tab and do it...

Hi all, I have this overview at the moment: If I manually choose to save my file as a PDF, it works fine and I get just 1 page with the overview above. However, I have the following VBA code to save my file as a PDF: Option Explicit Sub SaveFileWithMacro() Dim Path As String Dim fn As...

Hi all, 2 questions: 1) I have the following setup: I add new files quarterly to my excel file, so Q1 2021, Q2 2021, Q3 2021, Q4 2021, Q1 2022, Q2 2022 etc. Right now I only have one for each year, but I plan to add many more going forward for 2022- Does anybody have a way in which I can...

Hi all, I currently have the following setup: I have some numbers in column C and D, and I use a simple sum() formula in column B. However, I also want to be able to click on the cells in column B and see the value in the formula bar. But I want to keep the formulas in the meantime. What I...

Hi all! I currently have this setup, please note that I have hidden all the nonrelevant columns however: The formula I use is this (thank you @RoryA !) =IFERROR(INDEX(INDIRECT("'"&H504&"'!$U$3:$U$73"),MATCH(1...

Hi, I'm struggling to find the way in how to use xlookup function, to obtain data from another workbook, but without using the workbook name/path. I want to avoid using the name of the workbook and the path, because its going to be changing each day both name and location, earlier in the macro...

Hi all, Is there an "or" function per-se for lookup values under XLookup? Below I have a table that has a couple of different name combinations. I would like to be able to lookup both name variations in the table below (Preferred name column AND the reverse name column) and then return the...

By the title, you've guessed I'm struggling to find the right path for what seems pretty basic. But trying to adopt the formulas from the posts and the videos leads me to #REF! despair! The attached Workbook has been reduced to the important parts: A worksheet called "Pine Lakes Front" and...

Hi Am using a Wildcard Search in an Xlookup =Xlookup(”*”&A1&”*”,B5:B199,C5:C199,,2) Works fine if the lookup cell contains a value, when the Lookup cell does not contain a value matches with first cell to contain a space anywhere, cannot work out a way to stop this behaviour, Does anyone...

Hi, I am using xlookup to pull some information from another sheet in the same workbook. The 'return array' is formatted as currency (not sure if this matters), but its not pulling as currency. Its pulling as a general number, but when attempting to format the destination cell to currency it...

Hi, I have a table that has below format and need to update the amount from different table that has a different format, I tried using Xlookup and Index/Match but for some reason it didn't work. Could anyone help me? What I need to accomplish: - The template I have has 3 columns (Employee...

Hi all, I currently have this VBA code: Option Explicit Private Const FilePath As String = "S:\COMPANY\FOLDER OF COMPANY\FOLDER 15\TEST FOLDER\Attachments\" Sub send_email_complete() Dim OutApp As Object Dim OutMail As Object Dim i As Long Dim ws As Worksheet Dim col As...

Hi all, I currently have this setup: My VBA code is this ( credits to @RoryA :) ) Option Explicit Private Const FilePath As String = "\\UBSPROD.MSAD.UBS.NET\userdata\t684895\home\Documents\faq folder\" Sub send_email_complete() Dim OutApp As Object Dim OutMail As Object Dim i...

Hi all, I currently have a data overview that draws data through power query from a folder in my windows computer: However, I plan to update this folder rapidly every week and I was wondering if it would be possible to create two macros attached to VBA codes, where they do the following: 1)...

Hi All, I am currently facing an issue in with my pivot table that I have been absolutely unable to fix and it does not seem like the Power Pivot forum is very active compared to this one. In addition, mine is more related to Pivot Tables than Power BI, Query or anything like that, so I may...

Dear all, I currently have this VBA code (please note the part highlighted in bold): Option Explicit Private Const FilePath As String = "\\COMPANY.SSSS.COMPANY.NET\userdata\t5382304\home\Documents\TEST folder\" Sub send_email_complete() Dim OutApp As Object Dim OutMail As Object Dim i...

I want a formula to lookup a date, establish whats it falls onto using the week ending date, match that against the employee name and return the intersecting value. For example, I want to see James' potential hours for 8/1/21, which should return 32 (B16). If the date was 04/04/21 then it...

Hi Guys, I am trying to use the following formula : =XLOOKUP($B8,'[December 202021 WORKBOOK.xlsx]Sel'!$B:$B,'[December 202021 WORKBOOK.xlsx]Sel'!M:M) the return array being '[December 202021 WORKBOOK.xlsx]Sel'!M:M I need it to actually return the N:N column so i can copy it across so it...

Is there a way to prevent xlookup to bring in duplicate data? A1: Source B2: Formula The data is things like 1 - Segment, 2 - Field, 3 - Range and they repeat several times but I don't want to them to repeat in B2 but rather just pull 1 instance of each. Maybe I need a filter formula in a...