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
8 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
21 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
19 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
27 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
16 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
10 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
21 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
12 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
8 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
48 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
59 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
27 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
23 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
76 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
15 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
83 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
46 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
46 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
189 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
13 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
58 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
126 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
16 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 ...
1
vote
3answers
33 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 |
...
0
votes
2answers
41 views
A formula for importing specific data into another sheet
I need a function for column K of Sheet1, that says:
"If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2"
...
0
votes
2answers
32 views
Google Sheets Lookup For Nth Value
I am working on a spreadsheet that seeks to collate scores to a team. There is an input table where team and score start next to each other (see the example sheet linked below) into a table with each ...
1
vote
1answer
640 views
Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?
I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
1
vote
1answer
22 views
Lookup another sheet get whole row in sheet and no duplicates
I have looked through the questions but somehow I just can't find what I am looking for.
What am I looking for?
I have a document with 2 sheets.
Let's say, sheet 1 is the backlog, sheet 2 is the ...
0
votes
1answer
204 views
Count consecutive columns with the same values?
Before I get started, here is a link to the sheet example:
spreadsheet sample
Essentially what I want to do is Count the number of times the value in C5 occurs consecutively in its first occurrence.
...
0
votes
0answers
67 views
Pull data using a condition where there are multiple results in Google Sheets
My data includes multiple rows like the following:
Country | Company | Num of Activ. | Most recent? | Full URL | Init. Rank | Curr. Rank
Country A | Company 1 | 1 | Yes | ...
1
vote
3answers
201 views
Copying data from one cell in another based on date
I have a spreadsheet, using google sheets, that I have formed into the following metrics:
Column A is the dates of the month
Column B is the data
I want to be able to copy the data from Column B ...
1
vote
1answer
164 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 ...
0
votes
1answer
366 views
Vlookup across multiple columns
This question is related to the same project in this question, but I'm now at a later step.
I have a Google Sheet with a variety of German translations of a large set of English words. Using a ...
1
vote
1answer
708 views
VLOOKUP Google Sheets not finding entry from formula
Here is a link to my sheet.
I am trying to do some base 36 to binary encoding and VLOOKUP claims to not be able to find the values of column E even though they are clearly there.
I've narrowed down ...
0
votes
0answers
1k views
How can I make a multiple criteria index,match array formula work?
I've been using array formulas in my google Sheets a lot, of the type:
={"Heading";arrayformula(if(len(A2:A)>0,FORMULA HERE,""))}
in order to "auto drag down" a formula when the A column is ...
4
votes
2answers
948 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 ...
0
votes
2answers
425 views
INDEX & MATCH / VLOOKUP counting number of found matches instead of showing one of them
I have two sheets in Google Sheets. In one I have two columns with values I need to find in two columns of the second one and return number of matches for each row in the first one.
=Iferror(INDEX ( ...
0
votes
3answers
4k views
VLOOKUP using cell reference with IMPORTRANGE gives error
I can't see what I'm doing wrong with this.
This Doesn't Work
=Vlookup(L2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Le0cMfbHRSgXqhCdzjQrBscFNm6p1sL-DowWdCZ0Fs/edit#gid=1761671943","...
-1
votes
1answer
386 views
In Google Sheets, I'm trying to create a drop down that reads from 2 columns but returns from 1 column [duplicate]
Basically, I have store number and Store name. I want to be able to start typing a name and select the name, but only show the store number.
If that is not possible, then I thought I can combine ...
1
vote
1answer
2k views
How to return array of cells from a table in Google Sheets
We have # EXAMPLE TABLE with product SKUS on the top row, and the first columns is ingredients. Each SKU row is showing the value of the ingredient row. What we want to do is have another sheet with a ...
1
vote
1answer
880 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 ...
0
votes
1answer
500 views
Arrayformula+VLOOKUP find value of non-empty reference (callback/recursion problem)
Similar to An arrayformula to find the previous non-empty cell in another column.
I have two worksheets like this:
"" = Blank cell
() = Sheet Name
Worksheet One (Form Responses 4) is the input ...
0
votes
1answer
301 views
Filling a column with a formula that references correct row without dragging down
In Google Sheets, I am trying to create a formula that will automatically apply to every cell in a column. The formula refers to another column of cells in a 1:1 way, that is S3 should refer to B3, ...
-1
votes
1answer
398 views
Vlookup in external spreadsheet
Is it possible to use VLOOKUP with an external spreadsheet?
SpreadsheetA contains a set of a few hundred addresses.
SpreadsheetB is an invoice.
I want to use VLOOKUP in the invoice to return ...
0
votes
1answer
315 views
Lookup a value?
Is it possible to look up a value based on a value entered in a field - just like the VLOOKUP function in Excel?
1
vote
2answers
1k 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
58 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
686 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
3k 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
178 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(...
