Questions tagged [xlookup]
144 questions
2
votes
1 answer
Using XLookup to give latest record by date, but if exact match isnt found it is giving false info
I am using a formula that I learned from this article: https://exceljet.net/formulas/xlookup-latest-by-date?fbclid=IwAR3rDjbzoAUQ5vrqo7QRXJt1VxvVjBuZ7kxHE7_YA-lfl53gx1OMl7MI2GM
Basically, I need to find the most current record of for a custmer based…
cheese10234
- 111
- 8
2
votes
2 answers
How to sum the value of 2 rows with vlookup by only using 1 formula?
Here is what I did
=VLOOKUP(M3,P2:Q23,2,FALSE)+VLOOKUP(N3,P2:Q23,2,FALSE)
I want to sum the values with just one formula and not repeat it
Im using Excel Online
I tried =XLOOKUP(M2:N2,P3:P23,Q3:Q23) but I get a value error,does anyone know how to…
MochA
- 23
- 3
2
votes
1 answer
Excel XLOOKUP - find and return data containing text, but exclude if it includes other text
I have xlookup function searching range F1:F44 for the text in F2 and then returning the match:
XLOOKUP(TRUE,ISNUMBER(FIND(F2,F1:F44)),F1:F44,,2)
However I need to exclude entries if they include text listed in array A1:A10.
I have tried switching…
asaysno
- 35
- 6
2
votes
1 answer
Excel: How to return a column using Xlookup dynamically
Im trying to specify which column to return when using Xlookup but not sure how to go about this. Sample data below:
My formula =XLOOKUP(1,(C3:C6="Bob")*(D3:D6="LA"),G3:G6) gets the value of column G where name is Bob and city is LA returning 78.8…
West
- 2,350
- 5
- 31
- 67
1
vote
2 answers
Why is my XLOOKUP, with a nested MAXIFS, unable to give me an exact match?
First off, here is my formula:
=XLOOKUP(MAXIFS(D:D,E:E,">0"),(B:B=F2)*(A:A="EFP")*D:D,C:C,,-1)
CAMPAIGN
RECORD PHONE#
REP #
PAYMENT DATE
PAYMENT AMT
LEAD PHONE…
cheese10234
- 111
- 8
1
vote
2 answers
How to change a multiple-criteria xlookup to visual basic
I am trying to change an Excel xlookup formula to visual basic to reduce the file size. However, I cannot quite get the code right. There are two criteria to match, when both criteria match, then a value from my return array (in a different…
Saira
- 15
- 4
1
vote
1 answer
How can I use Application.XLookup across every sheet in my workbook?
Here is my code, first off:
Sub namelookup()
SalesForm.BHSDTAPNAMELF.Value = Application.XLookup(Val(SalesForm.BHSDMAINNUMBERLF.Value), Worksheets("NICK TAPS").Range("S:S"), Worksheets("NICK TAPS").Range("T:T"),…
cheese10234
- 111
- 8
1
vote
1 answer
VBA/Xlookup with multiple criterias advice
Hi I am trying to do a xLookup on emails in different columns to check which one is duplicated.
The idea is to lookup an email in column, if it doesn't exist then move on to lookup in another column. For example, if Range("E1:E6").Value is empty…
Khai
- 17
- 5
1
vote
1 answer
xlookup formula with more than one criteria
i am trying to match a value using xlookup formula to find I1 in A and if H1>C and H1C,H1
k1dr0ck
- 1,043
- 4
- 13
1
vote
2 answers
How to return a value based on two dynamic (non-dependent) drop down lists in excel
I am creating a recruiting funnel that has two dropdown lists on the "dashboard" sheet that references a data table on another sheet. The dropdown lists are "location" and "job title". The other sheet called "Data", has a table with AREA_TYPE (US,…
Seanf0912
- 13
- 4
1
vote
2 answers
How do you flag cells by comparing values in another column with the same 'ID' column?
I have a sample table like this:
base
I wanted to flag if there is an increase in Output of the same ID compared to the previous Date.
Note that the data is only sorted by Date.
Expected output:
output
I've been trying to find the correct formula…
babooshka
- 13
- 3
1
vote
1 answer
Index and Match with 2 criteria
I have 2 excel file/ worksheet. One is internally maintained within the company and one is generated from SAP system. My task is to find out if the 2 files are matching or not. If not what is different, the one maintained in Input Data 2 is the…
AshSam123
- 21
- 3
1
vote
1 answer
is there any specific function to bring value to a selected cell without vlookup?
knowing only date, time, and DRND code (starting TP and numbers), and amount, can i bring the card code to a next to a cell from a different sheet?
and
I was trying to do this
!could not do vlookup](https://i.stack.imgur.com/nHfAH.png)
both sheets…
noway to interrupt
- 25
- 4
1
vote
1 answer
Excel: TEXTJOIN with XLOOKUP
I need to look up values and separate the multiple matches with TEXTJOIN.
Here is the input data on Sheet1:
ID
Name
4003
Bob3
4003
Tom3
4003
Jim3
4004
Bob4
4004
Tom4
4004
Jim4
4005
Bob5
4005
Tom5
4005
Jim5
Sheet2: with…
EA Bubnoff
- 195
- 2
- 11
1
vote
3 answers
How to get specific match from a column of multiple possible matches using xlookup?
Table 1 below is the before table:
Acro.
Type A
Type B
ABC
CDE
EFG
Table 2 below is the reference table that I'll use for table 1:
Acro.
Types
ABC
A
ABC
B
CDE
B
EFG
A
Below is the end result of table 1 that I…
LordPuggo
- 79
- 7