Stack Exchange Network

Stack Exchange network consists of 174 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’).

1
vote
1answer
8 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
2answers
21 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
1answer
10 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 ...
0
votes
2answers
37 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
21 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
426 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 ...
0
votes
0answers
47 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
1answer
101 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
354 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
547 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
811 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
291 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
357 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
701 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
429 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
259 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
362 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
303 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
54 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
572 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
2k 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
151 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
243 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
907 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 (...
3
votes
1answer
371 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
2answers
195 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 ...
2
votes
1answer
51 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
229 views

VLOOKUP function in Cognito?

I want to fill automatically name, address, etc. when a customer fills its customer code. Is there a function like Excel's VLOOKUP in Cognito Forms? Where should I host my database and how can I ...
2
votes
1answer
475 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
489 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 ...
3
votes
1answer
4k 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 ...
1
vote
1answer
21 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 ...
5
votes
1answer
13k 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 ...
0
votes
2answers
1k views

Query reference to several sheets

I have this formula: =ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 ...
0
votes
1answer
301 views

How do I accomplish a VLOOKUP if my key range is discontinuous?

So, I've got a chart (an EXP chart for an RPG) with several break points in it. One column is how many EXP is required to get to a level, the other column is what level you get to be given that EXP ...
0
votes
1answer
1k views

Using VLOOKUP with dynamic range array

I have a sheet with a list of email addresses that is being auto-updated (more email addresses being added on a daily basis). I have a second sheet with a list of email addresses that have been ...
4
votes
1answer
6k 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) ...
1
vote
1answer
100 views

Importing columns of data

I'm trying to import columns of data from different sheets. Examples: SEARS -- (sheet one) (I'm entering the prices manually) 1/2013 $8 2/2013 $5 3/2013 $14 Here is sheet two: (I'm entering ...
1
vote
2answers
416 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
2answers
12k views

How to get value of cell by row key and column key

Suppose I have a workbook with two worksheets, data and view. data is structured in "database" style, i.e. with a single header row and unique keys in the first column, e.g.: Country GDP ...
1
vote
1answer
554 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
4answers
1k 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 ...
0
votes
3answers
2k views

Editing of a VLOOKUP formula that references a pivot table

After inserting a VLOOKUP formula in a Google spreadsheet, where the array parameter references columns of a pivot table, how do I edit the formula again? When I enter the formula, it just displays ...
3
votes
2answers
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" ...