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’).
148
questions
5
votes
1answer
17k 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
222 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
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
7k 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
2answers
590 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 ...
3
votes
1answer
459 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
2k 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
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
649 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
43 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
110 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
646 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
38 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
75 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
1answer
59 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
18 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
636 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
3answers
295 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
40 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
1k 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
229 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
266 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 ...
1
vote
2answers
2k 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
227 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
514 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
591 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
111 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
27 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
83 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
30 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
214 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
37 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
23 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
29 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
25 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
183 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
129 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
30 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 ...
1
vote
3answers
719 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
437 views
Google Sheets VLOOKUP with wildcards on range
SETUP
Say for a simplified example I have a table of raw data from my bank displaying each store I've spent money, and I want to categorize these into either high-street, website, or pub venues.
The ...
1
vote
2answers
2k views
Arrayformula for entire column in Google Sheets with a lookup not working
I have been working on automating a reporting sheet I have and discovered the very handy use of arrayformula in Sheets that autofills the column with your formula based on data in another column.
...
1
vote
1answer
65 views
Summarize the best options in another table
I need help to create a sheet for a game. I am providing the INPUT, and I would like to create the OUTPUT in another tab (do not consider the colors, its just for ease of understanding):
Mainly, the ...
1
vote
1answer
964 views
Google Sheets - Sum of vlookup in one cell
Hitting a brainfreeze, can't figure it out.
Problem:
I can't figure out how to add together Table#1 Sam WC
results as a nice table for Table#2. What I need is the results in Table#2.
Table #1:
| ...
1
vote
1answer
4k views
Validate dropdown from 2 columns
Sheet 1 has column A called CONTRACTOR
Sheet 2 contains CONTRACTOR details. Column A has Company Name, Column B has Contact Name
If I start type a name in column A on Sheet 1, how would I get ...
1
vote
1answer
230 views
VLOOKUP stops working after 500 rows
In Google Sheets I have a search box that brings up the information for a person when you type in their id. It works perfectly up until ID 512, which is row 374. The formula I use:
=IFERROR(VLOOKUP(...
1
vote
1answer
622 views
Return cell reference containing closest value
I'm trying to get a spreadsheet to tell me which cell(s) contain values closest to a value I provide. Without going into too much detail it's for a golf game I play, and I've done some data collection ...
1
vote
1answer
22 views
Subclauses in formulas?
Quite often I end up including the same calculation in a formula twice, like this:
=if(iserror(vlookup("lookup",SheetX!A2:X350,5,false)),"",vlookup("lookup",SheetX!A2:X350,5,false))
I'd like to ...
