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’).
130
questions
0
votes
0answers
29 views
How do I add values from one sheet based on a dropdown that references the other?
I have 2 sheets with the same columns with integer values. In sheet A I have a drop down list of the names in sheet B. Based on what drop down I have selected, I'd like to add the values of sheet B to ...
1
vote
0answers
10 views
Arrayformula with iferror and vlookup does not update automatically
My spreadsheet is for inventory checking. I use several of arrayformula ,iferror and vlookup.
The problem is I just noticed that sometimes the formula does not update. It might because I duplicated ...
0
votes
1answer
16 views
Having trouble outputting GOOGLEFINANCE data
My wish is to create a table in Google sheets looking something like this:
price
day A B C D ... Z
1997-01-02 132.54 . . . .
1997-01-...
1
vote
1answer
46 views
Get last value entered past a specific row
I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has ...
1
vote
1answer
23 views
Returning max value based on two conditions
I've been reading through a lot of other solutions to similar problems but am not able to resolve my issues.
I have a Google Sheet with two sheets I am working with:
Sheet 1
A list of contacts (...
0
votes
2answers
13 views
Query twice in the same cell to receive a name next to a number in a row
Sheet link
I posted before however, I don't think I was specific enough. I am sorry if I am wasting the time of anyone.
In my sheet, you will see three pages. On the "Search Notes" page in C5 I ...
1
vote
1answer
19 views
GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low
I'm using Google Finance calls in Google Sheets for some calculations.
I've got 2 formulas, one to find the low price, and one to find the high price from a certain date:
=MIN(INDEX(GOOGLEFINANCE("...
1
vote
1answer
25 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
21 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
16 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
14 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
27 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
25 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
15 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
48 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
31 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
16 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
108 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
16 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
31 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
21 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
21 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
32 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
13 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
24 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
12 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
75 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
179 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
51 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
31 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
50 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
8 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
30 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
13 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
41 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
47 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 |...
