211 questions
0
votes
1
answer
64
views
Excel: Need to look up a random value in a row next to a corresponding entry and return a result in another list
I normally try to figure these our or my problems don't have that much complexity, but trying to create a list for testing. The list will have 4 unique values that are considered the attribute and ...
- 155
0
votes
1
answer
68
views
How to use ARRAYFORMULA with multiple criteria XLOOKUP or INDEX MATCH? (SHEETS)
I want to add in some additional information to a sheet that is populated by Google Forms from another tab in the same worksheet.
I want to have a formula which looks up a value based on multiple ...
- 1
-4
votes
2
answers
130
views
XLOOKUP with RIGHT function [closed]
Is it possible to use the RIGHT function with XLOOKUP?
I have the following formula that refuses to work
=XLOOKUP(RIGHT(A2, 3), Sheet1!$A$3:$A$200, Sheet1!$B$3:$B$200, "")
Any alternatives ...
- 167
0
votes
0
answers
19
views
Double xlook up
I want to double look up where i look up the sku and col header and get the value.
The image here shows me looking up the sku and then finding the description value
"=XLOOKUP(A4,$A$20:$A$26,...
1
vote
2
answers
64
views
Selecting column based off of current month
I have an excel spreadsheet of values that gets imported into Power BI. The columns of the spreadsheet are based off the months of the year, and the rows then correlate to user values during that ...
- 60
0
votes
1
answer
65
views
Trying to search a specific column range of 45 to 64 only
My formula works but if 44 or less or 65 or greater number is entered, I get “XLOOKUP couldn’t find the requested value”. That’s why I want to limit the search to only include a number range from 45 ...
2
votes
3
answers
146
views
VBA Code using Xlookup with a table won't compile
Good evening, all,
I'm trying shorten names in a column by parsing the names and doing lookups on each word to return a possible abbreviation. I was doing a loop through each row (~200 words) to find ...
- 65
0
votes
1
answer
280
views
How do I apply ARRAY Formula for XLOOKUP?
I have an XLOOKUP function that is working the way I want. (I've wrapped it with some validation functions)
=IF($A$2:$A<>"",IFNA(XLOOKUP($A2 & B$1, ARRAYFORMULA('Unit Update'!$B$2:$...
- 1
1
vote
1
answer
139
views
Google Sheets - Array Formula with Xlookup and Importrange
I have a Sheet which basically adds name and address info from a membership list to a list of bank payments.
LINK TO EXAMPLE SHEET
It uses an Xlookup (in Column G) to compare the Bank Account Name (...
- 13
1
vote
2
answers
76
views
Search for Value in a table
This has me stumped.
I am working with a scenario where I need to look for a value in a table. I'm familiar with the index-match formula, but I'm having a hard time setting it up because it seems that ...
- 85
0
votes
1
answer
318
views
Returning Multiple Columns With Xlookup With Multiple Lookup Values
I've been trying to return multiple columns with Xlookup with multiple lookup values, but only the first column is returned. Nevertheless when I provide only one lookup_value it works well.
=xlookup(...
0
votes
2
answers
306
views
Complicated xlookup across two sheets with multiple criteria
I have a worksheet that I want to pull data from one sheet to another but there is some logic that makes it complicated.
Master sheet with the raw data:
COL_H
COL_I
COL_J
COL_K
COL_L
SKU_R
SKU_L
...
- 33
0
votes
3
answers
165
views
I want the result of a function to return the value at the top of the column in an array instead of the position of the cell it's searching for
I have the following function:
=ADDRESS(SUMPRODUCT((P29:AD37=T11)*ROW(P28:AD28)),SUMPRODUCT((P29:AD37=T11)*COLUMN(P28:AD28)))
which returns the position of the value it's searching for. I would like ...
0
votes
1
answer
85
views
Xlookup multiple match types
I have a table that has a list of items, and the date in which the item changed price, however not all items change price every day so Im trying to set a formula that would return the exact match for ...
0
votes
1
answer
60
views
How to fix a #VALUE error when trying to pull an entire row of information from multiple sheets
I am trying to create a home inventory list that will eventually combine a recipe list that will automatically subtract amounts form the inventory. Right now I am stuck on pulling information from the ...
0
votes
1
answer
767
views
Using Xlookup with multiple criteria while checking if it contains text, instead of just exact matching
I am sure this may be a bit confusing, but I have a database similar to this screenshot:
What I want to do is, based on the data on "Info" ranges, apply a value to the Value ranges. For ...
0
votes
3
answers
165
views
How can I use excel functions to identify whether an enter date is a holiday?
Hi,
I have a dataset that includes all weekdays, displayed in blue, and another dataset containing holidays from 2024 to 2026, shown in green. I would like to identify which dates in the weekdays ...
- 13
0
votes
1
answer
154
views
XLOOKUP, INDIRECT, CONCATENATE, and other misadventures in file referencing
I have several file paths I need to adjust dynamically.
Parts of the file path change based on year and month. I have named cells assigned for the various components and have attempted to use concat, ...
-3
votes
2
answers
135
views
Running XLookup from VBA UserForm [closed]
I have a project to create a database of materials.
I develop my module code in sections (kind of like sandboxing) and then throw it in with everything else once I have it figured out.
I'm stumped on ...
0
votes
2
answers
287
views
How to use the output of a CONCAT() function as an array in Excel
I would like to use the output of a CONCAT() function as the title for an array within XLOOKUP(). The XLOOKUP() formula I am using that works is:
=XLOOKUP(MetricDesired,'2023-12-13_000_Report.txt'!$C:...
- 13
0
votes
2
answers
226
views
Getting the CELL ADDRESS of XLOOKUP result produces "ARGUMENT MUST BE A RANGE" error
The function works perfectly when xlookup is constrained to a single row to search...but when I search multiple rows for my match, suddenly I get an error saying the argument must be a range.
I tried
=...
1
vote
1
answer
139
views
Is any way to change XLOOKUP behavior?
From the source data table
Name1
Name2
ID
Wanda
Al
1
Olin
Normand
2
Harriet
Brock
3
Harriet
Brock
16
Wanda
Al
14
Olin
Normand
15
i would like to take each row with the first occurrence of Name1 (E2:G4)...
- 5,611
0
votes
1
answer
41
views
How do I sum the 7 cells beside a correct match using Vlookup?
I have two Google Sheets. One is a list of dates in sequence in Column A (9/1/24; 9/2/24; etc..) and in Column B are number values associated with some of the dates.
Sheet 1:
9/1/2024
9/2/2024
...
0
votes
1
answer
488
views
Function XLookup not return correct results
How can I lookup a value in 1 sheet, compare the value of column in another sheet, if match found then return the value of column (I), have it displayed in abc.C2, etc for the entire spreadsheet?
=...
2
votes
1
answer
533
views
XLOOKUP equivalent in Power Query
I have a simple XLOOKUP formula that looks up a string in a master_list sheet and if that exists in the weekly_update sheet, it should update the contents of selected columns in the master_list with ...
- 420
1
vote
1
answer
82
views
Excel - Increment a counter if data is found
I am working with some sports data and am attempting to dynamically pull game info into another excel sheet. I have created an excel formula that does mostly what I want but I want to see if I can ...
- 125
1
vote
3
answers
159
views
XLOOKUP finding ranks for scores ignoring place values, returning wrong rank
I use xlookup all the time, so this is absolutely stumping me. Using a simple XLOOKUP formula to compare a list of values and find the resulting rank from a list of ranks. If I ONLY compare the score ...
- 180
0
votes
1
answer
75
views
Formula for comparing rows for matches in multiple columns
I am creating a net-worth calculator in Excel. I am planning to enter the bank and equity details once a week. I want to know the change in my portfolio compared to the last entry using a function. I ...
- 11.7k
0
votes
1
answer
104
views
Pass textbox entry to XLookup formula
I have a variable from a textbox. When I use it inside the xlookup formula it can't find the item.
LotNum is the name of the text box, it can be numbers and letters.
Public ICN as Variant
ICN = ...
- 31
0
votes
1
answer
121
views
XLookup in VBA Not Returning Value
I created a UserForm for data display only. I'm comparing the data in the UserForm against a tab labeled "MARKETING_FORM" within the same excel Workbook.
Eventually if the two do not match ...
-1
votes
1
answer
266
views
Excel - How to calculate the return value when the lookup value falls between two values in the lookup array
Assume I have this lookup table:
Column A
Column B
60%
10
70%
9
80%
8
90%
0
100%
0
110%
0
And the value I want to match is 65%
I'm trying to find a formula that will give me the number that's in the ...
1
vote
4
answers
984
views
Sum values based on XLOOKUP
The table below shows a list of employees and number of hours they have worked in that week. I am trying to come up with formula that would return the total number of hours worked per employee per ...
- 61
0
votes
1
answer
195
views
Excel Formula for Today's Month Being Greater Than Lookup Value Month & Provide Message
I have created a budget sheet in Excel to track Debt Snowball payments. I have a formula that successfully works to lookup the month from a list of months (column F) that a final payment is made for a ...
1
vote
0
answers
532
views
How to sum values until a certain text value is reached
Below is a sample of my data
This is data from a pipe model I created. I am trying to create an excel formula that returns the lengths between bend points (i.e. summing all lengths of "run" ...
- 11
0
votes
1
answer
112
views
Index or xlookup needed - looking to return multiple critera xlookup to same row
Thank you in advance for your assistance. Looking to do either a index/match formula or xlookup (open to any other solutions) to create this view in my sheet.
I am wanting excel to look at column N ...
0
votes
0
answers
350
views
xlookup with mutiple criteria
I'm creating a fantasy football spreadsheet and I'm looking to use double xlookup to return data based on two criteria.
I lookup the values in "draft day" column B in "schedules" ...
- 25
0
votes
1
answer
69
views
Using XLOOKUP in a scatterplot series
I am working in a flight manager workbook where a Departure and a Destination airport can be selected. Once the airports have been selected their coordinates are fetched from a different worksheet ...
- 11
1
vote
1
answer
90
views
Excel search for certain text within cell and return vlookup from a range
In Table 1 I have a column of Names in one tab (column H) and would like to bring in the Contact information (column I) based on a list (columns A and B) in a separate tab (Table 2).
Table 1 example:
...
- 107
0
votes
1
answer
316
views
Comparing 2 Excel tables using XLOOKUP function cell by cell
I'm trying to compare 2 tables cell by cell, and what I want to achieve is getting '#N/A' (using XLOOKUP function) in every cell that differs from the first table.
So, I would like to see "#N/A&...
- 3
0
votes
0
answers
130
views
How to use XLOOKUP based on lookup values seperated by commas
I have three types of measurements I can take in each room (s/r/b), test numbers, and the room number in which they were performed. One whole test needs a s, r, and b measurement to complete. To save ...
- 13
0
votes
0
answers
43
views
Google spreadsheet / FIND vs Xlookup solution / find value in array
can anybody help ?
Example -
sheet / table one in the row will be name and surname, in the column header date dd/mm/yyyy/, for the name, if person is not present --> will be letter H (holiday).
in ...
0
votes
0
answers
564
views
Lookup Previous Cell Value in Excel
I need some help in creating an excel formula where it will lookup the previous code based on the column name. Please my sample data below:
Column A and B is being added on file daily, what I'm trying ...
0
votes
1
answer
215
views
Xlookup in excel doesnt function in Makearray?
There appears to be an error in excel formula when combining XLOOKUP with MAKEARRAY formula:
e.g.
This is a simplified example. I have used y values of constant increments to make the formula easier ...
- 927
-1
votes
1
answer
4k
views
Excel: Lookup '1' and return multiple values
This seems rather simple be cannot currently find a solve for this.
I am currently in a spreadsheet, which has a column that is returning a binary value on the basis of random sampling.
I need to ...
- 3
1
vote
2
answers
185
views
Excel - Summing Multiple Material IDs Cost from one Cell
So, I have a Product that is made of multiple materials, labeled with material IDs. I need to pull the multiple material IDs from the cell and find their associated costs from another table, and then ...
- 35
0
votes
1
answer
7k
views
How do I compare two sheets in excel and if there is a match copy and paste the description (cell associated to the match)?
Sheet 1:
PO NUM
Comments
51773
51775
51777
Sheet 2:
PO NUM
Comments
51777
xyz
63814
abc
51773
ghi
63814
def
51775
jkl
I am having trouble getting the PO Number to search and match between the sheets ...
0
votes
1
answer
115
views
arrayformula disappears when sheet is sorted in Google Sheets
I have created a document template to help me automate breaking down scripts for my job as an Art Director for Film/TV. I've made good progress with automating the colour coding needed and auto-...
-1
votes
1
answer
993
views
XLOOKUP using R [closed]
I am trying to achieve XLOOKUP function from excel in R (as in attached picture). So far no luck with it. Any suggestion on this can be done in R Below is the code and example dataframe
dataF <- ...
- 65
1
vote
1
answer
60
views
Dynamic X Loopup using Tables - How to tell xlookup which table to use?
Currently the formula in cell B3 works by only looking up the value in B2 in the "Total Debt/Capital" table of cells D1:K6. How do I make the formula in B3 dynamic so it will change which ...
- 47
0
votes
0
answers
179
views
Xlookup returns different values when used on same workbook vs. external workbook
I'm trying to compare data recently pulled from a database into an excel spreadsheet to one pulled about 2 months ago. When I use Xlookup to find my array in the current workbook I get one array based ...
