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
1
vote
1answer
31 views

How do I arrange raw data in Google spreadsheet in group way?

I have a list of orders, like this: Paid $28.00 | 2019-04-08 15:14:20 Unpaid $00.00 | 2019-04-08 15:13:48 Unpaid $00.00 | 2019-04-08 15:12:58 Unpaid $00.00 | 2019-04-08 15:12:10 Paid $18.00 |...
0
votes
1answer
39 views

Value transformation in Google Sheets

I'm working with a table of data as following: User ID: Event name: 111 Event 1 222 Event 2 333 Event 3 111 Event 4 444 Event 5 111 ...
-1
votes
1answer
40 views

Creating a formula based on TODAY's day

I have column A (list of stock prices at close of day) A1=4/1/2019 | stock price A2=4/2/2019 | stock price A3=4/3/2019 | stock price each day will be added to the sheet automatically I want to take ...
0
votes
1answer
209 views

ArrayFormula date vlookup -30days in first 30 rows of Google sheets

I am trying to use an array in Google Forms Response sheet for a time sheet. I want to have a rolling 30 day total for hours worked. The first 30 days is not able to lookup 30days prior. ={"Previous-...
0
votes
1answer
42 views

Importrange doesn't import data from extensive sheets

The formula works on lower column number that I need to get data from but nothing is happening when I need to import data from columns with a higher number. Following formula works (data imported ...
0
votes
1answer
546 views

Linking a dynamic inventory price list to another file in Google Sheets

I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers ...
1
vote
4answers
10k 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 ...
0
votes
1answer
1k views

MATCH a cell value, then return the value of the cell next to it

Is there a way to use a MATCH formula to look up where one value appears and then use that returned index as the point to reference to find another value? Right now I have a spreadsheet set up where ...
0
votes
2answers
108 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
814 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
27 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
74 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
796 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
36 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
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 ...
0
votes
2answers
581 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 ...
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 ...
0
votes
1answer
1k 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
471 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
312 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 ...
2
votes
2answers
462 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
21 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
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 ...
0
votes
2answers
852 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
345 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 ...
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 ...
1
vote
1answer
39 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
510 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
2answers
1k views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
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 ...
0
votes
1answer
200 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
49 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
votes
2answers
292 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" ...
-1
votes
2answers
656 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
2k 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
32 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
749 views

Count consecutive columns with the same values?

Before I start, 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. In ...
1
vote
3answers
4k 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
930 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
426 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
1k 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 ...
1
vote
1answer
367 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 ...
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 ...
0
votes
2answers
2k 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
8k 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
769 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
5k 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 ...
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 ...
0
votes
1answer
1k 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
426 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, ...