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’).
162
questions
5
votes
1answer
18k views
VLOOKUP issues not finding a value that's in its range
Here's a link to a copy of my spreadsheet.
I am attempting to do the same formula using a VLOOKUP in four sheets to do a calculation called LSI.
In the first sheet I have the raw data from a form ...
4
votes
2answers
2k views
How to update VLOOKUP results in Google Sheets to most recent input from Google form?
I am trying to create a formula that pulls responses from a Google Form and organizes them into rows correlating with a client's name. The name is entered manually in and also appears in a drop-down ...
4
votes
2answers
231 views
Pick the first value in every row that has not been picked yet
In the example I am giving below, I would like the result in Column G to be what is in Column C, unless it already exists in Column G in rows above it:
These rows represent students' choices for ...
4
votes
2answers
2k 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 ...
4
votes
1answer
7k views
Multiple conditions look up
I am having trouble building a spreadsheet, the problem its my data table. I want to be able to pull data from the table, something that will match data from column A, and column B (on the same row) ...
3
votes
1answer
8k views
How to VLOOKUP in a QUERY
I Have this sheet with raw data, basically names, dates, amounts of money and if they paid or not (this is the most important). What I am trying to do is to have in another sheet the same table but ...
3
votes
1answer
464 views
Partial match for strings that are truncated beyond 30 characters
I have two sheets. Sheet one has a list of events in column N: "IEM Katowice", and others. However, our system imports event names that are longer than 30 letters (including spaces) with a .. at the ...
3
votes
3answers
3k views
Create a dropdown list of options from VLOOKUP
What I'd like to do is get a dropdown list of options from a VLOOKUP type function where I can select the value for the next lookup.
Example
Here the data I'm using for reference. Notice "AAA" ...
2
votes
2answers
179 views
Categorize text in Google Sheets based on matching partial text in the Category index
I'm looking to match, with VLOOKUP, part of the words in a column in the main sheet to an index of keywords/categories where partial words are stored for ease in another sheet.
Here's an example ...
2
votes
1answer
1k views
Use an array/list as VLOOKUP search key
I have two sets of data. The first is an ever growing list of names, which we'll call Names (not necessarily a named range). Something like:
John
Bill
Bob
Steve
The second is also a growing set of ...
2
votes
1answer
45 views
How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D
I'd like to populate Columns C and D in an entirely new spreadsheet, where Column D still auto-populates the cells just as it does in the original/first sheet (as it does in the photo attached inside ...
2
votes
2answers
376 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 ...
2
votes
1answer
797 views
Formula in Google Sheets to lookup contents of another cell
I'm trying to create a formula that brings back a certain cell depending on what another cell has in it. But the formula I'm trying isn't working. It's working for other things (what I thought was the ...
2
votes
1answer
56 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:
+-----------------+-----...
2
votes
1answer
126 views
Searching a value between two columns on a reference sheet
I have my main sheet that looks like this:
The Compromise column (Column F) bases its value on another sheet. To compute its value, we check if the value of the Basic Tax (Column B) falls equal or ...
2
votes
1answer
864 views
How to perform a VLOOKUP with search column dynamic depending on input in another cell
I have the following table:
What I want to do is as follows:
User selects either Minor, Medium or Major to specify which column is used.
User then inputs a number 1-100 and the chart then looks to ...
2
votes
1answer
29 views
Searching cells and pulling values in Google Sheets?
sorry for the vague title but I'm not sure how exactly to describe this.
I've created a spreadsheet and used the Coinmarketcap API to pull lots of data, here's the relevant part:
From another sheet I ...
2
votes
1answer
57 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
2answers
81 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 ...
2
votes
3answers
1k 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 ...
2
votes
1answer
759 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 ...
2
votes
1answer
64 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 ...
1
vote
4answers
2k views
How can I convert a number according to a range?
I'm working with Google Apps (spreadsheet) and I need to convert results (ranging from 0 to 36) to a maximum of 7 according to the following table.
if score is (0-5) result should be 1
if score ...
1
vote
4answers
1k 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
...
1
vote
4answers
6k 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 ...
1
vote
3answers
441 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 ...
1
vote
3answers
41 views
Summing up amounts if one row contains a key within another sheet
I have two sheets in one Google Sheets document:
| Key | Amount |
|-----|---------|
| ABC | 1 |
| DEF | 2 |
| GHI | 3 |
| Keys |
|-------|
| ABC |
| XYZ |
| JKL |
| GHI |
...
1
vote
1answer
54 views
Formula to find the cell in column C where a value in column A = a value in column E
I'm trying to create a formula in a google sheet to find the value in the C column where the value in the A column is equal to the value in the E column.
E.g. Column E = 1 in Row 9-16 and Column A = ...
1
vote
1answer
3k 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
2answers
343 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
2answers
3k 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 ...
1
vote
1answer
302 views
Pull a value for the following month in Google Sheets
I have a list of values, and they are all for a variety of months (Jan 2018, Feb 2018, &c.). Google Sheets automatically converts these values into 1/1/2018 and so on. Is there a formula I can use ...
1
vote
1answer
1k views
VLOOKUP problem with value from =arrayformula(iferror(regexextract
I'm trying to use VLOOKUP to pull data from one tab in a Google Sheets workbook into another tab. The VLOOKUP formula works if the value being searched for is 100103, but not the cell reference G2 (...
1
vote
2answers
520 views
VLOOKUP or INDEX+MATCH
I have an array of values:
A B C
1 TypeID MaterialTypeID Quantity
2 18 34 256
3 18 35 512
4 20 34 386
5 20 36 773
...
1
vote
1answer
598 views
The column from which to return VLOOKUP responses depends upon the value of a cell
With Cell F1 a drop down menu, I'm trying to look up the value of A2 on sheet 2 and if F1 contains:
"Test1 1/2", then return the data in the 3rd column
"Test1 2/1", then return the data in the 4th ...
1
vote
1answer
130 views
Google Sheets Inverse (or Reverse) LOOKUP … is this possible?
As far as I can tell, all LOOKUP functions require you to know a specific search_key (like a column or row header) in order to search for and retrieve a relative value.
Inversely, is there a way to ...
1
vote
1answer
286 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 ...
1
vote
1answer
28 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),...
1
vote
1answer
137 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) ...
1
vote
2answers
31 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
vote
1answer
857 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
40 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
vote
1answer
49 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
47 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
vote
1answer
46 views
How to get a cell's parent ID in Google Sheets?
I'm trying to bulk add a bunch of building product categories to my site's MySQL database. There are about 650 entries and about 4 levels in the hierarchy. For it to work, each entry needs an ID, and ...
1
vote
1answer
28 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
530 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:
...
1
vote
1answer
165 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 ...
1
vote
1answer
25 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
33 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 ...
