All Questions
Tagged with worksheet-function vlookup
216
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
99
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
0
answers
374
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 ...
- 931
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
329
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
0
answers
91
views
VLOOKUP and Sheet Name using MID formula
I have a work sheet where VLOOKUP working properly but not work when i add a cell value that contain sheet name using formula
=MID(CELL("filename",A1),FIND("]",CELL("filename&...
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
68
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
0
votes
1
answer
46
views
How can I compare column values from two different work sheets?
I have two different Worksheets (A and B), both of which contains the columns PackageName and PackageVersion, both Column B and Column C respectively in each worksheet. I would like to have a third ...
0
votes
2
answers
89
views
Return Specified Values From Another Sheet
I have two Excel sheets open. The first sheet has data organized by Student ID and by School (two different column). In the second Excel sheet, I simply want to return a column that shows the Student ...
- 13
1
vote
3
answers
4k
views
VLOOKUP record with latest date
I'm creating a spreadsheet to monitor the last time we contacted a customer. In tab "Overview" I have a list of all the customer names and addresses for example:
A
B
C
D
E
F
Name
Address
...
- 25
2
votes
1
answer
2k
views
Excel: Get sheet name from Drop down Cell for formula
I have sheets each with an employee name.
I have a Summary Sheet that gets the weekly total from each employee sheet using VLOOKUP.
On the summary sheet, I have a drop down cell with all employees ...
- 23
1
vote
1
answer
37
views
Excel Lookup from different sheet
i have sheet in below formats
Sheet1
Sheet2
Now i have to validate my Sheet1 as per Sheet 2 if ID exist and country present its "Match" else "No Match" if no entry then "No ...
- 95
0
votes
2
answers
427
views
If VLookup Function that add corresponding cells if 1 range matches another
If B4 matches a # in column A I need the formula to return the # in column C and then add...
For example: Since B4 matches A1, I need C1 (90) +E1 (3) + F4 (0) and return the value in C4.
I tried this, ...
0
votes
3
answers
1k
views
Excel VLOOKUP for range of dates
I have been struggling with VLOOKUP for sometime. I want to use VLOOKUP for a range of dates using the two tables I have given. I want to get which Term each row belongs to. Basically I want to check ...
- 101
1
vote
1
answer
303
views
How do I count multiple levels of referrals (A referred B, B referred C, D & E, E referred F, G, H, I, etc)?
I have a referral system where you get credit for referring other customers. If those customers refer other customers they get some level of credit for those as well. We need to count not only how ...
- 119
1
vote
1
answer
646
views
Copying cell colors to another sheet
My active sheet (let's call it Sheet1) will be main for all users to edit and view.
User 1 only needs to view half the data in Sheet1 at a time so I've created a Sheet2 to only display the data that ...
- 11
0
votes
2
answers
7k
views
return yes or no based on cell data from another sheet
I have two sheets.
The first sheet "Example A" and the second sheet "Example B".
Example A has a master sheet of clients that may also be on Example B. If the client is listed on ...
- 1
1
vote
1
answer
386
views
Excel - Concatenating cells returned through looking up against duplicate values
I have a unique problem that I have been unable to solve and hoping to get insights from the experts here. I have the below table:
Sprint ID# Description
1 ABC
1 XYZ
2 ...
- 11
0
votes
3
answers
3k
views
Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells
Is there an option or setting that prevents VLOOKUP from returning zero when it finds blank cells or does not find the lookup value.
I do not want to suppress the zeros by formating them out. I want ...
0
votes
1
answer
3k
views
Increment the cell in Vlookup when dragging horizontally in Excel
I have a Vlookup formula in excel
=VLOOKUP($A2, Kenya!$A$2:$L$34,2,FALSE)
I'm trying to drag this formula in columns (to right side of the sheet) and I want the the row of the lookup value to ...
0
votes
3
answers
3k
views
EXCEL: Number of days since last visit
I am trying to calculate the number of days since a specific person last visited a location.
Looking at the following sample dataset, what would be the best method?
I tried making an index column and ...
- 3
0
votes
3
answers
281
views
Excel formula help multiple criteria drop down
Hi,
I am hoping for some help in a V look up when using two drop downs for todays date (G1 & G2).
The table on the right are calls made each day will contain data of calls made by employees, it ...
- 37
0
votes
1
answer
170
views
VLOOKUP if Client ID is customer during the same month during different years
I have a list containing four columns: Client ID, Year, Month, Recurring customer.
Each transaction during a month creates a row. I want to see if the same Client ID has a transaction on the same ...
- 23
0
votes
1
answer
220
views
Inputting several values, found from a VLOOKUP function, into a COUNTIF function
This question is about a single function (in a single cell to later extrapolate over a whole table) in Microsoft Excel.
I want to find a unique id on another page (amongst other id's in the first ...
- 103
1
vote
1
answer
65
views
Excel - lookup by multiple criteria
Based on the attached screenshot, I have a sample database on the right side.
I would like to input random models, capacity, and grade on the right side, and receive an output of pricing.
How would I ...
