Questions tagged [vlookup]
For questions about the VLOOKUP spreadsheet function that allows the mapping of a selection (a ‘search key’) to a result in the same row of a table (‘range’ or ‘array’).
123
questions
1
vote
1answer
17 views
Query sum for unique pairs of rows
I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example.
City Person Days
London Tom ...
0
votes
2answers
15 views
Help with a Vlookup function to return multiple results of not exact match
I am trying to run the following code to return all the matches of column A from column C in a set of rows. In this situation, cell A1, is contained in a few iterations of column C, however, these are ...
0
votes
1answer
12 views
Assigning a value to a name from a list
I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0
votes
1answer
12 views
Can I populate Google Sheets cells adjacent to a target cell?
I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
1
vote
1answer
25 views
How to search value in another column?
Using Google Sheets, I am trying to two compare two columns on Google Sheets, like the one below:
Basically, I want to compare column A and B, basically, if a cell in column A is inside a cell in ...
1
vote
1answer
23 views
How can I change the formula of one cell based on the value in a different cell?
COLUMN D: Dropdown list of values
COLUMN E: IF function returning different values depending on COLUMN D contents
I have the functionality described above working properly. My problem is that I want ...
2
votes
1answer
14 views
Dynamically creating column names based on data in another tab
I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted.
The ...
0
votes
1answer
15 views
Pull data when cell value is selected
I am Trying to pull data from part of a sheet to another part depending on if dirt level 22 value is filled in. Please see attached Picture, basically, the bottom section pulls data from rows above ...
1
vote
1answer
30 views
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows
I've been trying to get this array formula to work, but I get an error. It's just a vlookup array to display multiple columns. It works fine without the {""; bit, but I want it to drop down one row.
=...
1
vote
1answer
17 views
Outputing Array Formula in one row
I came across the below array formula, which works really well, I'm just struggling to tweak it, and barely understand it's inner workings, but it's very good. Instead of displaying a total of stock ...
1
vote
1answer
27 views
Array Formula with IF function and SUMIFS function
I am trying to convert the below formula into an array formula. I can make it work in an array, but it displays the wrong values, compared to the normal formula which I have to drag down.
Normal ...
1
vote
1answer
15 views
Count unique values in second column corresponding to unique value in first column
I have a Google sheet with cities corresponding to countries like this. Notice that neither countries nor cities are unique and there can be duplicates.
| A | B |
----------------------...
-2
votes
1answer
23 views
How do I find latest invoice number & dates?
Trying to find a formula for the latest date and invoice number.
Here is my sheet:
https://docs.google.com/spreadsheets/d/1T_rFYDCQhqfrsU9claYPHQTrEz68zpUFKG9HZqqV4Z8/edit?usp=sharing
0
votes
1answer
44 views
Repeat formula n number of times in Google Sheets
Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
1
vote
1answer
15 views
How to get cell's parent ID?
Firstly thank you for helping me out. I'm in the construction industry and don't have any clue how to best do this - not my forte!
I'm trying to bulk add a bunch of building product categories to my ...
0
votes
1answer
29 views
Fetch value of a cell based on dropdown menu items results on another sheet
I have 2 Sheets named as Sheet1 and Sheet2...
Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column.
Sheet2: ...
0
votes
1answer
16 views
Importrange (or maybe another part of formula) behaves differently after a short time period
I am using this formula to look for data in column F related to data in column A between two spreadsheets.
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; {IMPORTRANGE("spreadsheetID";"sheet1!A4:A")\ IMPORTRANGE(...
1
vote
1answer
12 views
If a cell equals to this date, then filter through another sheet and grab that cell that has the same date
When you input a date in sheet 1, I want it to automatically grab data from sheet 2 and input text that matches that date back into sheet 1
0
votes
1answer
14 views
Having a formula output to a different cell
I have a Google Sheet in the following format:
ID | VAL
-------------
1 | dataH
2 | dataW
3 | dataX
4 | dataC
5 | dataG
6 | dataL
... | ...
ID and VAL are on cells A1 and B1, ...
1
vote
1answer
8 views
Help using 2 parameters to output a specific category
I'm looking to be able to enter an "AGE" and a "V02" in a formula that tells me what "category" the person would be in ie; good, excellent, etc.
0
votes
1answer
8 views
How do I search a string, and return all values?
How do I use the script editor to search a string in one cell, and return all results from another sheet?
For Example:
I have my string in cell B1 - which is the item number Data to be searched in a ...
1
vote
1answer
14 views
Full Join 2 lists with 3 key values
I'd like to fully join 2 tables on 3 key values, there doesn't need to be 0 value on blank cells if it makes it easier to understand (Some Array Formulas can be really confusing)
I have income:
I ...
1
vote
1answer
26 views
Left join 2 sheets on 2 key values, add constant value
I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet,
Income:
Expense:
Add FixedCost to every row/date:
...
0
votes
1answer
12 views
How to pull data from a random cell on page load in Google Sheets?
Basically, I'm just trying to figure out how to make a cell pull a random cell's value within a range at random when the page loads.
For example, have cell A1 pull a cell at random from range B1:B20 ...
-1
votes
2answers
23 views
Google form wrongly delete rows in second sheet
I copied a previous question here since it's exactly the same as what I want to ask:
I created a form in Google Docs. Then I created a second sheet to pick
and process data from the first sheet ...
1
vote
1answer
10 views
Search max value of column after individual operation on each value of that column using arrayformula or similar
I have a Google Sheets with a column D with identifiers in a certain format (i.e. Y19_XXX):
Y19_234
Y19_213
Y19_814
Y19_215
Y19_817
...
Now I want to determine the highest value XXX in that column ...
2
votes
1answer
43 views
Add missing dates to list of dates with values
Current Data:
9/20/2018 6
9/22/2018 3
9/23/2018 1
Desired Output:
9/20/2018 6
9/21/2018
9/22/2018 3
9/23/2018 1
I would like to have the missing dates added to Column A while the ...
0
votes
1answer
124 views
Auto-populate cells based on previous cell values in Google Sheets
I have two columns with information. Values in Column B have been manually added to match the values in Column A.
Is it possible to have a function that auto-populates column B with the right values ...
1
vote
1answer
34 views
Alternatives to dragging formula down?
I'm having trouble applying the following formulas to entire columns in Google Sheets. I think that an array is the best option because there are 5000+ rows, but any help is appreciated. I'm really ...
0
votes
1answer
23 views
How to use arrayformula to find and get data from only a certain, dynamic set of columns?
I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order.
In the example below I've got the header values:
AA - BB - CC - ...
0
votes
2answers
7 views
Look for a value that might be in many columns
Say I have a value in $A, to match to another sheet of the same file. That value (in this case, a student id) might appear in many columns of the other sheet.
In the following example, I am looking ...
0
votes
1answer
29 views
Look up a list embedded in a Google Sheets from a Google Forms, and add attributes to the corresponding row?
I'm running an event for a large number of children and need a way to let them register quickly as they come in, to the event.
We're expecting about 400 kids who have pre-enrolled and we will have a ...
0
votes
1answer
33 views
Count in QUERY to include counts for all unique items (dates)
=query(A:B,"Select A, Count(B) where A is not null and B='A' group by A,1")
0
votes
1answer
7 views
Sorting column A with drop-down and column B with checkbox
I have a spreadsheet that I use to run a foosball tournament, and in column A I have a dropdown data validation that will let me select a players name. In Column B I have a checkbox that I check once ...
0
votes
1answer
26 views
Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)
Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook).
Sheet 1: ...
1
vote
1answer
12 views
Automatic cell coloring via conditional formatting
I am working on a sheet for a game I play and having some trouble with cell coloring.
I have one sheet, named "PlayerList" which maintains a list of player names, class and role. Players are ...
1
vote
1answer
16 views
Selecting row with best value for items with several versions/upgrades
I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help.
I have a huge table, but the problem boils down to this: some items have 2 versions (one ...
0
votes
1answer
8 views
How do I copy over a value from another worksheet depending on the adjacent column?
I have two checklists of the same items in a different order in two separate sheets, and I want to only update one. Basically, I have two pages that look like this
And I want the? to be automatically ...
0
votes
1answer
32 views
Replace value in field automatically (auto-correct?)
I'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098.
Is this possible?
0
votes
1answer
16 views
Collates the number of depots that don't have manifests and trunk headers
Trying to do a work project that collates the number of depots that don't have manifests and trunk headers.
Column A: Depot Numbers
Column B: No Manifest record (i.e if Colum B 2 - 200 contains "...
0
votes
1answer
15 views
INDEX + MATCH to return the greatest of all matching values
I'm looking for the most recent date corresponding to a particular name. Dates are not necessarily ordered. I can't sort the target, I need it to work in any order.
Example: When was [Roberto]'s most ...
1
vote
1answer
30 views
How to filter only the highest value for each date based in a criteria?
I have a table with 3 columns. I want to filter only the highest value of column B for each date (A) if column C contains the code "red".
My sheet:
date entries code
08/04/2019 1 red
08/04/...
0
votes
1answer
12 views
How do I arrange raw data in Google spreadsheet in group way?
I have a list of orders, like this:
Paid $28.00 | 2019-04-08 15:14:20
Unpaid $00.00 | 2019-04-08 15:13:48
Unpaid $00.00 | 2019-04-08 15:12:58
Unpaid $00.00 | 2019-04-08 15:12:10
Paid $18.00 |...
0
votes
1answer
18 views
Value transformation in Google Sheets
I'm working with a table of data as following:
User ID: Event name:
111 Event 1
222 Event 2
333 Event 3
111 Event 4
444 Event 5
111 ...
0
votes
1answer
7 views
Creating a formula based on TODAY's day
I have column A (list of stock prices at close of day)
A1=4/1/2019 | stock price
A2=4/2/2019 | stock price
A3=4/3/2019 | stock price
each day will be added to the sheet automatically
I want to take ...
0
votes
1answer
21 views
ArrayFormula date vlookup -30days in first 30 rows of Google sheets
I am trying to use an array in Google Forms Response sheet for a time sheet. I want to have a rolling 30 day total for hours worked. The first 30 days is not able to lookup 30days prior.
={"Previous-...
0
votes
1answer
22 views
Importrange doesn't import data from extensive sheets
The formula works on lower column number that I need to get data from but nothing is happening when I need to import data from columns with a higher number.
Following formula works (data imported ...
0
votes
1answer
41 views
Linking a dynamic inventory price list to another file in Google Sheets
I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers ...
0
votes
3answers
258 views
How Can I Sort a Data Range in Google Sheets Based on an Data Range?
I have been looking for a way to sort this information. I need to sort these items in this order: BK, BB, BV, BIV, BIII, BII, BR. I was trying to make a Google Script to get it, but I could only ...
0
votes
1answer
22 views
MATCH a cell value, then return the value of the cell next to it
Is there a way to use a MATCH formula to look up where one value appears and then use that returned index as the point to reference to find another value?
Right now I have a spreadsheet set up where ...
