All Questions
Tagged with vlookup microsoft-excel
451 questions
0
votes
1
answer
20
views
How to make my VLookup function use a cell value as the table_array ehich reference a table from another sheet
I am trying to make it so creating a new page is simpler for others when I leave my position. So I want to make sure they have to change as little as possible to generate new sheets. My work refuses ...
0
votes
1
answer
36
views
How do I use VLOOKUP to lookup multiple values if a value is potentially missing?
I have a table with two columns:
account | amount
--------+-------
525 | 199.12
400 | 209.23
410 | 458.78
415 | 56.10
150 | 119.60
This data is being pulled in from a SQL server ...
- 11
0
votes
1
answer
39
views
Excel lookup by column name
Here is a scenario -
I have quantity table like this
100
200
$1
$3
$2
$4
I have more columns with other values like 300, 400, 500 and so on. Also, each row has a name associated but I am new to ...
0
votes
1
answer
27
views
Find the difference in days inside a Pivot table
I'm trying to find the difference in days within a Pivot table or just within the table itself. I have the following situation (I'll add the table to copy on the bottom of my question)
I have several ...
0
votes
1
answer
87
views
Value of Cell D4 disappear if any cell in Column A that has the same value in Column B as D4 is marked as true
I'm trying to create a list that will compile ingredients from different recipes into a list of the total ingredients needed, but exclude ones from recipes that have already been made. I know how to ...
1
vote
2
answers
55
views
Excel vlookup oddity
I have a simple table with rows Low, Medium, High.
I’m trying to use a VLOOKUP to see the value associated with that but I get #N/A when searching for the value High. Only high fails to look up. ...
- 115
0
votes
0
answers
27
views
I need to pull a date from a row if the vacation column has a number in it
I have a time card sheet. I want to compose a short list of all the dates a person has used PTO/VAC and the amount they used on that date.
I have all the dates listed for the year in one column. ...
0
votes
0
answers
40
views
How do I update a masterlist with already existing data with new monthly data in Excel?
For the sake of simplicity, I only provide a simple masterlist with not more than 10 rows.
So here's a masterlist:
The masterlist has all assets waiting to be updated monthly. The list only has data ...
0
votes
0
answers
40
views
IF AND Formula excel
Current formula is:
=IF(VLOOKUP(B2,CPRS!A:AX,50,0)="D2C Exclusive","Yes","")
HOWEVER, I want to add to the above the below if the result is ""
=VLOOKUP(B2,CPRS!...
- 21
0
votes
1
answer
65
views
Vlookup inconsistence or confusion(?) using true range lookup
I want to make a commission that is within a certain percentage range. However, the results given are not matched, instead the grade is. Does it have anything to do with the percent value?
The ...
0
votes
1
answer
174
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
67
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
47
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
50
views
IF formula using vlookup
I am trying to create an IF() formula using VLOOKUP(), my VLOOKUP() is this
=VLOOKUP(A14119,'CoO Flags Report'!D:L,9,0)
but I want it to state no if the VLOOKUP() finds #N/A
- 21
0
votes
1
answer
245
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
189
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
1
vote
1
answer
506
views
VLOOKUP ERROR. Works for some cells, does not work on others
So, I have a vlookup formula
=VLOOKUP(A2, Vouchers!E:K, 7, FALSE)
and it's been copied down the table and has been working great. But suddenly, it has stopped working for new rows.
I double checked ...
- 13
0
votes
0
answers
58
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
0
answers
19
views
Using excel to reference other workbooks, but using a cell to tell excel which other workbook to reference
I am trying to input data into a cell, and have the cell next to it reference data in a separate file to tell if if there is a match in a specific column in that other file. The goal is to use this ...
- 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
93
views
How can I perform a VLOOKUP by updating only the newly added data each month in a main database while preserving the previously looked-up information?
I have 2 tables, the main data base with data that gets added monthly, with a name, date and corresponding rating. I also have a lookup table that has names and rating of that person, the rating of ...
0
votes
1
answer
151
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
542
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
999
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
52
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
503
views
Vlookup with search and left to find partial string
I'm trying to use a vlookup to pull information from one sheet to another but the reference information is different. For this example I put it on one sheet to make it easier. The information is ...
- 25
0
votes
0
answers
112
views
Vlookup error with drop down
I have created an expense sheet with month drop down list.
When I choose months from January to November, the data shows correctly.
But for December, it's incorrect.
November:
December:
Sheet3:
[3
...
- 1
0
votes
1
answer
414
views
Max value from multiple criteria with VLOOKUP
I want to find and display the highest number based on a grouping. Column A has a group of names with an item number at the end. Column C has individual item names and Column D has a number ...
- 25
0
votes
1
answer
196
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
237
views
Populate rows (year listing) based on drop down selection MS Excel
I have a challenge presented to me. I have searched quite a bit, but I cannot find a solution that exactly works.
I have a drop down listing that represents the number of years (1-50). I need to ...
- 1
0
votes
0
answers
38
views
VLookup using Excel sheet to get data from another workbook
I have two Excel workbooks:
one.xlsx two.xlsx
'[one.xlsx]Sheet1'!A:A contains a list of 50 text strings.
Each string is the name of one of 50 worksheets in two.xlsx
where _NAME_ is one of those ...
0
votes
1
answer
74
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
117
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
2
answers
7k
views
Comparing two columns across different sheets and returning a value from the 2nd sheet
my tables look somewhat like this:
Table 1
Name
Code
ABC
UH000511
CBS
BY044551
CLM
CZ004491
LBU
UA955100
Table 2
Company Name
Code
ABC Capital
UH000511
Central Bank of Scotland
BY044551
...
- 25
1
vote
0
answers
102
views
excel storing values that are being inputted daily
I have the following input in my excel, which somebody fills in manually every day. The date columns have the following formulas: =WORKDAY(TODAY(),-1) and =WORKDAY(TODAY(),-2)
At the beginning of ...
- 11
0
votes
1
answer
595
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
1
vote
1
answer
115
views
Create hard-coded domains to autopopulate columns with aliases instead of codes in excel
I work with layer databases exported from ESRI ArcGIS Online. On ArcGIS, attribute tables have many columns (or fields) with codes. For each code corresponds a specific alias based on the properties ...
- 127
1
vote
2
answers
1k
views
IF condition with partial match of an indexed VLOOKUP column - Excel
I have this sample of a spreadsheet to simplify what I'm trying to do:
In "Formula 1" I'm using a VLOOKUP function within an IF condition to assign value_if_true (and value_if_false) ...
- 127
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
2
answers
54k
views
Vlookup with Blank cell
Vlookup with Blank cell
I have a Vlookup formula that I want to return "" if the value is empty.For example: =Vlookup(D2, sheet 1,A2:D2,3,0)
- 13
-1
votes
1
answer
748
views
Compare two sheets and show a summary with only rows where a specific column has changed for that primary key, without altering source data
I'm trying to generate a report that will regularly compare current raw data with that of previous reporting period and only show rows (primary keys) where a certain column's value has changed between ...
- 123
2
votes
1
answer
2k
views
How to create dynamic sheet references with cell text in Excel
I am working with a large workbook and I want to drag formulas across to reference the title of their column with the corresponding sheet of the same name.
In this example: =VLOOKUP($B3,'04.22 NE'!$B$...
- 21
0
votes
1
answer
106
views
Finding aggregate return for multiple entries under same name
I would like to find the aggregate return in an excel for trading entries.
I have 3 columns, A - name, B - Buy, C - Sell.
For instance Sally first entry is Buy 2 and then later on Buy 3, and her last ...
- 101
-3
votes
1
answer
119
views
Joining tables with one key and multiple repeating values of the key on the other [closed]
I'm struggling to combine two sheets using the column of one as a key, and XLOOKUP is no help since the second sheet has multiple rows that match each of the key rows in the first.
Sheet A has 65 ...
- 1
0
votes
1
answer
197
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
162
views
Xlookup or vlookup
Hoping someone can help me out with some excel magic -
I have two sheets.
Sheet 1 has a list of names in column D
Sheet 2 has a list of names in column B and corresponding ID's in column A
I need to ...
- 291
0
votes
1
answer
3k
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
62
views
How to get adjacent cells of 3 smallest numbers in order?
I need 3 smallest numbers and corresponding cells. As you see first two 2s are A and the last one is O, but the output gives me A anyways. I would like to get them from the top to the bottom, so my ...
- 15
0
votes
1
answer
399
views
How to return adjacent cell value of the lowest cell
I'm trying to use VLOOKUP formula from this site but it doesn't work for me for some reason. It shows #N/A. How could I solve it?
- 15
0
votes
2
answers
238
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:...
