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