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’).

Filter by
Sorted by
Tagged with
5
votes
1answer
19k 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
3k 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
9k 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
467 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
466 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
2k 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
130 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
424 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
868 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
2answers
327 views

Array formula with index match to return array of matches

I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA. What I'm attempting to do is match a ...
2
votes
1answer
67 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
134 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
969 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
18 views

Using Vlookup with Dates

Here's the set up to the problem I'm having. I have a column of dates and a column of truck numbers. I'm trying to use VLOOKUP to find what truck numbers correspond to the dates. There are multiple ...
2
votes
1answer
37 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
81 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
87 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
1k 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
150 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
3k 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
8k 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
484 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
45 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
60 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
4k 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
403 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
4k 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
354 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
600 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
275 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
375 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
117 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 ...
1
vote
1answer
172 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
33 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
1k 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
42 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
71 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
56 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
65 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
29 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
655 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: ...