All Questions
201
questions
0
votes
1
answer
62
views
XLOOKUP - I need the lookup value to default to 2 decimal places
I currently have a spreadsheet that i want an occurance ratio, the lookup value is giving me 9 decimal places, how can i change it to 2 so i don't have a million lines with the lookup values?
Example: ...
0
votes
0
answers
36
views
Excel - Formula help - If A2 = !A2:A4, highlight !B2:B4 IFNOT = B2
I have 2 large workbooks of employee data and need to express the following in a formula.
Workbook 1:
Employee ID
First Name
00000001
Sam
00000002
Fred
00000003
Bob
Workbook 2:
Employee ID
First ...
0
votes
1
answer
41
views
Excel lookup/match bug - can find given but not calculated value, even if they're the same
I'm experiencing a very odd behaviour with Excel lookups and match: if I try a lookup or match for 7.03, it will find a cell, but if I lookup 7.02+0.01, it fails. For example:
A
B
1
7.01
a
2
7.02
b
...
- 23
0
votes
1
answer
100
views
How to convert a simple VLOOKUP to XLOOKUP in Excel?
The VLOOKUP works as intended. The XLOOKUP is giving "#NAME" error:
screenshot of the formula:
the working VLOOKUP:
=VLOOKUP(C12,B24:C29,2,FALSE)
Which I'm reading as "take the ...
- 5
0
votes
1
answer
63
views
How to get an exact match with VLOOKUP into the current cell
The name "Dodson" is on A5, the lookup is from B5, the table A16:B19, the value in the second column of the table, but am looking for the precise result of a VLOOKUP.
The formula I have is:
=...
- 5
0
votes
0
answers
53
views
Excel Vlookup Where Column to Search is identified in Preceding column
I am trying to figure out if there is a way to write a formula such that the Table_Array of a vLookup is based on a value from another column. Basically, I want the Vlookup to search on another sheet ...
- 1
0
votes
2
answers
45
views
VLOOKUP displaying wrong information in Excel
new to excel, and trying to do a simple task, where depending on the months in a month range, some percentage will be displayed. However, it's always displaying 0.
Here's a picture to help you ...
0
votes
1
answer
101
views
Using a ~ tilde in a Vlookup excel formula
How would I use a Vlookup with multiple tildes in the text string? It would look something like this:
SWBC ~ 7797_Dodge_Challenger ~ Video ~ New
What would the VLookup formula be?
0
votes
1
answer
296
views
How to Autoincrement the Column index number by dragging VLOOKUP formula down
I need to drag a Vlookup down and have the Column index number in to increase by 1 every row I drag it down.
So for example:
Row 1:
IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,**35**,0)<=0,"IGNORE",...
- 1
1
vote
3
answers
324
views
VLOOKUP where part of lookup-cell can be contained in lookup table
Most of the time, I use wildcards in VLOOKUP to get a match when my lookup table contains longer descriptions of which a part might match my lookup value.
I now have the opposite usecase; I have a ...
- 111
0
votes
2
answers
50
views
Vlookup formula or something else?
I am trying to compare two separate columns from two different sheets. If the data on the Columns A match, I am trying to pull data from Column B within the first sheet and have it outputted on sheet ...
0
votes
1
answer
114
views
Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?
I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...
- 13
0
votes
1
answer
58
views
How can I write an excel formula to track trends based on strings and numeric position in moving rows on separate tables?
In this example which simplifies my data set, I have two tables representing months. I would like to be able to track the trend columns month on month.
In plain language the question is
If "cats&...
- 13
0
votes
0
answers
98
views
VLOOKUP formula based on 2 different values instead of 1
I need to fill in a cell with a value (account number) based on 2 different values (account name and department name) in 2 columns.
I have a table with account name in column A, Department number in ...
0
votes
1
answer
330
views
VLOOKUP Name Error even after using formula wizard
I'm having a really hard time trying to figure what is wrong in by VLOOKUP formula. I even used the formula wizard to try to fix it.
Here is the formula I'm using.
=VLOOKUP(A10,Melaminedetails,2,FALSE)...
- 1
0
votes
0
answers
37
views
User with blank detail in output
As per the attached screenshot, I need a formula to derive user with a blank cell to be shown as pending. (Pending has been manually entered.)
- 1
0
votes
1
answer
155
views
Function to search for specific number and then to further search for the suffix
I have a huge amount of data to process in which 4 points with a related prefix needs to be subtracted from each other.
Example on the picture attached.
(let's call prefix a "ring number" ...
0
votes
1
answer
2k
views
Excel column: If cell value is 0 replace with value from cell to the left
I am an excel novice. I have started working with data strings of over 1000 rows. Some cells in a column have 0 values and must be replaced with the value of the cell to the left. Currently, I copy ...
- 1
0
votes
2
answers
162
views
Searching by Clicking a Cell
is there a function or some way to have excel return a result by clicking on a cell?
for example, if I have a list of groceries going down column B2:B10 and the isle they are located in going down C2:...
0
votes
2
answers
171
views
Return Closest Known Coordinate When Entering a New Coordinate - Excel
I have a sheet of known coordinates laid out as Col A - Point Number, Col B - X Value, Col C - Y Value, Col D - Z Value; see below image (a lot more rows in reality).
I am looking for a way of ...
- 1
0
votes
0
answers
69
views
In excel, how do I return values beside a searched string
I am currently trying to lookup a string value in a range and have it return the value from the cell above or below it. I have tried many formulas to no avail.=INDEX(B2:J49,MATCH($V$1,B2:J49,0)-1,1) ...
0
votes
2
answers
82
views
Lookup a specific rowvalue when the column depends on another cell (row/column combination)
I have an excel sheet with a very large, scarcely populated matrix.
The first column describes an item, and the columns after that whether an item belongs to a certain group. Each item can only belong ...
- 101
1
vote
1
answer
38
views
Vlookup search Bug
When I use VLookup to auto fill rows from a chart, it works for every search value except searches where the lookup value is CPT-X. In this scenario original formula:
If I change the lookup value to &...
- 11
1
vote
1
answer
197
views
VLOOKUP logic when range_lookup is TRUE but table_array is not sorted
Excel's VLOOKUP hint tells us to use range_lookup = TRUE only when the table_array is sorted. However, does anyone know what is the logic that VLOOKUP executes when range_lookup = TRUE but table_array ...
- 111
0
votes
2
answers
1k
views
Excel wildcards not finding the matches they should in VLOOKUP function
I have multiple product families I'm working with, and each family has multiple products within. Some families have more products than others, all product names are unique. On one sheet named "...
- 15
1
vote
1
answer
5k
views
VLOOKUP and HLOOKUP combo
I am struggling with the following (see snapshot) and I can't understand what I'm missing.
If the id matches, I would like to extract the value of that id in when the values in col2 matches the ...
- 111
1
vote
2
answers
90
views
VLOOKUP confusion
My formula in C2 = =VLOOKUP(LEFT($A$2,3) & RIGHT($B$2,1),A9:C10,3,FALSE)
I need to join the left three characters from A2, the last character from B2 and use this in vlookup.
Can someone please ...
- 127
0
votes
2
answers
62
views
VLOOKUP Values not corresponding
I have an excel file that contains Olympic Data for 2008, 2012 & 2016. I would like to lookup the event of my dataset in sheet 1 to the table array in VLOOKUP sheet to determine whether the event ...
- 7
0
votes
2
answers
269
views
From an Excel list of event attendees spanning 10 years, how do I create another list that pulls the first year of attendance for all my attendees?
I have a list of guests that come to my events. The list contains 10 years worth of guest check-ins in order by date (ascending). Based on this list, I want to create another list that shows the year ...
- 1
0
votes
1
answer
2k
views
File name on the basis of cell value in vlookup
Currently, I am using vlookup as per the below:
This is in Cell M3 of main book:
=VLOOKUP(L3,'C:\Users\vp\Desktop\xcl\day_data\[5460993.csv]5460993'!$A:$C,3,0)
The value in Cell Z3 of the main book ...
- 21