Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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(...