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’).
0
votes
1answer
7 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
16 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
8 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
18 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
20 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
21 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
19 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
5 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
23 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
16 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
5 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
17 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
9 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
17 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
15 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
13 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
20 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
10 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
15 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
6 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
18 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
18 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
28 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
108 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
16 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 ...
0
votes
2answers
27 views
Function to search columns and tally data
I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example...
M4 holds the ...
1
vote
1answer
30 views
Return a value from a column if multiple criteria are met
Can anyone teach me how I can return a value from a column if multiple criteria are met where if:
a cell in a column of another sheet contains a word in a cell (referenced)
the same row in another ...
1
vote
1answer
20 views
How to import values from the 2nd sheet based on the colum of the 1st sheet?
I have two sheets -
Sheet1 includes:
col 1 | col 2
[Name] | [phones ]
Sheet2 includes:
col 1 | col 2
[Name] | [e-mail]
I want to importrange if the name on Sheet1 is equal to name on Sheet2.
0
votes
1answer
29 views
Search for text in multiple columns in multiple worksheets
I have 3 columns (Col1, Col2, Col3). All 3 columns are in their own worksheet (WS1, WS2, WS3).
I want to
search each cell in Col1
see if that cell matches anything within Col2 in WS2 then output "...
0
votes
1answer
34 views
If cell contains a text string assign a value to another cell
This is my Google Sheet.
I have a number of "shopping" cells formatted to have a dropdown list of products/words using the (right-click) "data validation" function.
I have another cell I want to ...
1
vote
1answer
12 views
VLOOKUP needs to find value from a list and if its equivalent then
I have a problem where I'm currently using a VLOOKUP function on this sheet. LINK
What I wanted to happen is VLOOKUP would search on the List on G column and compare it to B column and if it matches. ...
1
vote
1answer
25 views
Conditionally format rows with partial matches
I have a Google Sheets table that looks like this:
(col a) (col b) (col c) (col d)
Company Cohort Session Count Group
--------------------------------------------
Acme ...
0
votes
2answers
23 views
Google Sheets Formula for Attendance Records
I have a very overwhelming and large attendance sheet of 350+ people that I need to keep track of. I have a very simple attendance record of 0 if you weren't there and 1 if you were. For one of our ...
0
votes
0answers
11 views
Vlookup doesn't work at all
I am planning a birthday party now, and people have some options when it comes to what to eat.
I have a sheet with the options called ¨FOOD¨, then I have an overview sheet where I would like too what ...
2
votes
2answers
52 views
Processing data from multiple cells and comparing results with another cell
For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the ...
0
votes
1answer
126 views
Getting data from another Google sheet that isn't in the same order
I have a very basic knowledge of deeper functions of Google sheets and solving this has been a little bit of pain in the butt so far.
I have a spreadsheet A
column A contains ID
column I that has ...
1
vote
2answers
37 views
Conditional formatting rule that compares against a range of values
I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample ...
1
vote
1answer
41 views
Creating a parent ID cell in Google Sheets
I've got a spreadsheet of various categories and I'd like to create a column which gets the parent name of that particular category.
They are currently broken down as follows:
01.01 Category 1
01.02 ...
1
vote
2answers
114 views
Cartesian Product with constraints
I have a really big database that I have to sort and divide as the example below:
F1 F2 F3 C1 C2 C3 C4 results
P1 X X X X X P1 ...
0
votes
1answer
16 views
Number in a cell linked with a text
I would like to ask if there is a solution to my problem:
I have 200 numbers from 1 to 200 which are linked with text. For example: 1 = car, 2 = bicycle and so on. I would like to write a number (1) ...
1
vote
2answers
253 views
Google Sheets Query with Date and Time
I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp.
Timestamp Cell = J2 - This cell is in the following ...
0
votes
2answers
53 views
VLOOKUP is not finding text value on other sheet
See this example spreadsheet.
I get #N/A value when I try to find bbb on sheet2 using formula =VLOOKUP(A1;sheet2!$A$1:$B$4;1;FALSE)
How to fix it?
0
votes
3answers
48 views
Finding row number from value which is in between ranges
I have attached a picture containing two column B and C. If my input to this table is 2.3, it should return me row number where my input value is greater than or equal to B column value and less than ...
1
vote
3answers
353 views
Find the closest value within a range of cells and output cell reference
There are values from F column to AJ column in row 30. My input would be available in C column in row 30. As given in the picture, my input would be 118.45, I would like to find the closest value to ...
1
vote
1answer
15 views
LOOKUP for data from other sheet
So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail.
So what I'd like to do is, ...
1
vote
3answers
74 views
SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM
Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I and the total income for each month in row L.
On a separate sheet I have ...
0
votes
4answers
217 views
multiple IF statements with between number ranges alternative
How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column)
with a rule set as follows:
01-10
11-20
...
0
votes
1answer
28 views
How to use vlookup or indirect to copy data from another sheet, without changing the value on update of database
So I've set up automatic MySQL sheet that gets the data from it every day at 8pm.
I've set up everything with vlookup, and tried indirect. But I can't figure out how to make sure the values don't ...
