Questions tagged [vlookup]
VLookup is a Microsoft Excel function that searches for a value in the left-most column of a table array (a range), and returns a value in the same row based on the column number from which the user wishes to retrieve the value.
515 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
16
views
Wildcard not finding text
I am trying to identify words in a cell using VLookup-wildcard and referencing a table of words and an accompaniying identifier in the column beside.Unfortunately my formula is not working. Any ideas ...
- 1
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 ...
0
votes
1
answer
40
views
The #N/A error is preventing sort and filter in Excel 365. The error is from unpopulated cell relating to a VLOOKUP formula at end of current records
I have created a spreadsheet to record medical records for my sheep. I have two VLOOKUP formulae: one for animal number based on animal name; the other for practitioner name based on alpha value.
...
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
504
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
1
answer
19
views
Count number of times 2 words appear in cells
can someone help me. I am looking to count how many times 2 words appear in a column full of job titles. For example I want to know how many Programme Managers there are in column D. Job titles in ...
- 1
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
0
answers
771
views
How to map value of a cell to another value, depending what it "contains" ? (not a full match)
I have a worksheet C1 with the following data:
A
B
sometext 21 oscar ABC john
sometext 22 foo XXX smith
sometext 23 lucy YYY pedro
sometext 24 bar ZZZ edward
In another worksheet C2, the ...
- 933
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