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
0
votes
1answer
2k views

Count in QUERY to include counts for all unique items (dates)

=query(A:B,"Select A, Count(B) where A is not null and B='A' group by A,1")
1
vote
1answer
41 views

Automatic cell coloring via conditional formatting

I am working on a sheet for a game I play and having some trouble with cell coloring. I have one sheet, named "PlayerList" which maintains a list of player names, class and role. Players are ...
1
vote
1answer
16 views

Selecting row with best value for items with several versions/upgrades

I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help. I have a huge table, but the problem boils down to this: some items have 2 versions (one ...
0
votes
1answer
11 views

How do I copy over a value from another worksheet depending on the adjacent column?

I have two checklists of the same items in a different order in two separate sheets, and I want to only update one. Basically, I have two pages that look like this And I want the? to be automatically ...
0
votes
1answer
18 views

Collates the number of depots that don't have manifests and trunk headers

Trying to do a work project that collates the number of depots that don't have manifests and trunk headers. Column A: Depot Numbers Column B: No Manifest record (i.e if Colum B 2 - 200 contains "...
1
vote
1answer
493 views

How to filter only the highest value for each date based in a criteria?

I have a table with 3 columns. I want to filter only the highest value of column B for each date (A) if column C contains the code "red". My sheet: date entries code 08/04/2019 1 red 08/04/...
1
vote
1answer
22 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
156 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
37 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
322 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 ...
1
vote
1answer
534 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
72 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
667 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 ...
2
votes
2answers
84 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 ...
1
vote
1answer
27 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
512 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
389 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 ...
1
vote
2answers
359 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
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 ...
1
vote
1answer
242 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 ...
0
votes
1answer
18 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) ...
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 ...
0
votes
2answers
690 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
212 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
2answers
3k 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 ...
1
vote
1answer
29 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 ...
1
vote
3answers
42 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
vote
3answers
458 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

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 ...
1
vote
1answer
837 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
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","...
0
votes
1answer
417 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
4answers
2k 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
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 ( ...
3
votes
1answer
8k 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
2answers
520 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 ...
2
votes
1answer
129 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 ...
0
votes
1answer
419 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
vote
1answer
1k 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 ...
-2
votes
1answer
260 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 ...
0
votes
1answer
351 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 ...
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 ...
1
vote
1answer
599 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 ...
0
votes
3answers
3k 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 ...
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 ...
1
vote
1answer
107 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 ...
2
votes
1answer
820 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 ...