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’).
145
questions
1
vote
1answer
95 views
Google Sheets calculate average on variable number of non-adjacent columns?
A Google Sheet is being used to insert markings for students tests.
When a single test is done by students, teacher assigns multiple grades for it. For instance, one marking for writing, one for ...
2
votes
1answer
36 views
How to find, out of many days, 1 day that is most profitable on Google Sheets?
Ok, I've been looking for this type of formula but not finding it. So here I am asking for help.
I'm a day trader on stocks and I want to analyze my performance. For example:
+-----------------+-----...
1
vote
2answers
90 views
“Error Did not find value '____' in VLOOKUP evaluation.”
Please see my public spreadsheet here:
https://docs.google.com/spreadsheets/d/11RMN7uN_woEQVzlDEkX6vSARTCGYYBjWs11ZDU-6Shc/edit#gid=1276951687
My goal is to get the Location Code from tab 1 into tab ...
1
vote
1answer
46 views
Google Sheets - If cell contains text that matches text from a column, return another cell
https://docs.google.com/spreadsheets/d/1GG7ekrD6CxxpowrRDm9DMvv3xHdvtAa8MHYCk4gI4D4/copy
1) If D10 (or I10 or N10) matches an exercise from the corresponding column to the right, return another cell (...
1
vote
1answer
26 views
Make cell range appear in different position to VLOOKUP?
I want to use VLOOKUP to search the highest value in a given range, and report to me what day it's the highest.
Example VLOOKUP for my actual data set in case if it matters:
=VLOOKUP(MAX(A3:A1001),...
2
votes
1answer
33 views
How to automatically calculate averages from ID in Google Sheet?
I've been looking around for an answer but couldn't find what I was looking for.
The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that:
Looks at the unique ...
2
votes
1answer
54 views
How to check if the values of two cells exist in two columns of another sheet
I am trying to make a class attendance system where the student checks in by submitting her name into google forms. The final result will be displayed in Google sheets by marking a check box after ...
1
vote
1answer
54 views
Google sheets Xlookup dont work but need results in table the max date on the condition of userlist
Need some help with some data processing.
Link of the sheet
Example - I need on :
Page-2-Seznam članov > Coloumn C : It needs to be the max dates.
The function need to search on the liste the ...
0
votes
1answer
55 views
In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action
In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
1
vote
1answer
71 views
Filter the row containing an exact word and highest value in Google Sheets
I have a table (Master sheet) in Google Sheets that looks something like this:
https://ethercalc.org/t8f0ze0kyx6a
I need to filter out an entire row of information on a separate sheet (Last Lesson) ...
0
votes
2answers
28 views
How do I “bin” results in Google Sheets?
I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale.
For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
1
vote
2answers
26 views
Searching a list and replacing value if any item matches any item in the other list in Google Sheets
I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
-1
votes
2answers
16 views
Value of Cell A1 could point to values of different cells
I am having difficulty with the following. Cell A1 could have values from 1 to 50. I have a listing on a separate table of different values that 1 to 50 represents. (I.E. 1 equals 1.68) I would like ...
-1
votes
3answers
22 views
How do I sort data by size and not in ABC order
I am new to Google Sheets and have only used Excel a little, so Google Sheets is a bit different. I want to sort our band uniforms in size order from smallest to largest but Google Sheets only sorts ...
0
votes
1answer
44 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
37 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
74 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
52 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
29 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
20 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
102 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
34 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 ...
-1
votes
2answers
43 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
24 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
39 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
31 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
36 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
16 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
20 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
969 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
22 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
116 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
27 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 |
----------------------...
-1
votes
1answer
25 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
1k 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
21 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
38 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
84 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
22 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
10 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
16 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
24 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
128 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
18 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
190 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
41 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
2answers
416 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
458 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
115 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 ...
