A Google Spreadsheets function that uses a search_key to look in the first column of a specified range and return the value in a column of the range, specified by a 1-based index, from the first row in which the search_key is found. An optional fourth parameter may be set to FALSE to ensure only an exact match is returned. The vertical version of HLOOKUP. Do not use for Excel which has [vlookup].
Questions tagged [gs-vlookup]
501 questions
2
votes
1 answer
"Google Sheet" ,Compare two columns & return value
I want to do some function like sql joint table.
But I really dont know if google sheet can do it.
I want to compare two columns and return the cost, how can I perform it?
ColA ColB ColG ColH
Type Cost …
LazyP
- 33
- 5
2
votes
1 answer
Google sheets - VLOOKUP inside ARRAYFORMULA
I am trying to do Vlookup inside an Array formula in Google Sheets.
I have two sheets, S1 and S2.
Each has 3 identical columns, Col A (Date), Col B(Name) and Col C(Payment-Type)
I would like to read the payment type from sheet 2 into sheet 1 based…
Prahlad
- 67
- 2
- 8
2
votes
2 answers
How to use a filter inside an array formula?
I have a sign-out form that I want to find out which ones are out using just formulas.
My thought process is to find the row number of the last instance of each label and if the "out" row number is higher than the "in" row number then the device is…
Bjorn Behrendt
- 1,204
- 17
- 35
2
votes
5 answers
Using VLOOKUP in an array formula on Google Spreadsheets
Effectively I want to give numeric scores to alphabetic grades and sum them. In Excel, putting the LOOKUP function into an array formula works:
{=SUM(LOOKUP(grades, scoringarray))}
With the VLOOKUP function this does not work (only gets the score…
Sam Brightman
- 2,831
- 4
- 36
- 38
2
votes
2 answers
How to link various Google spreadsheets using IMPORTRANGEs that contain VLOOKUP formulas without getting #N/A returned?
The problem:
When using IMPORTRANGE to pull data from a different spreadsheet, cells in the origin spreadsheet that contain a formule containing VLOOKUP often (but not always) return #N/A (ERROR: Did not find value 'xxx' in VLOOKUP evaluation). In…
PHMBWE
- 21
- 1
- 2
2
votes
1 answer
Vlookup next largest value
I have some values that I am trying to use vlookup on, the values increment by 4, i.e. (14,18,22,...) I am trying to use vlookup on these values. The problem is that the search key values increment by one, so if the search value is for example 15,…
DasPete
- 831
- 2
- 18
- 37
2
votes
1 answer
How to put the value of multiple cells in another sheet from a reference code
this is the problem: I have two worksheets, one that records and discriminates revenues and expenses:
And other that contains a table with the price, revenue and taxes for each product:
Now, I need function that receives the code (or the…
craftApprentice
- 2,697
- 17
- 58
- 86
2
votes
1 answer
How can I apply a formula to new, form-inserted rows of data in Google Spreadsheets?
I would like my Google spreadsheet to automatically apply formulas found in cells F2 to H2 to all new, form-inserted rows. You can view the spreadsheet here.
The formulas are:
F2: =IF(B2, "I"&B2,"--")
G2:…
user2339317
1
vote
2 answers
"Joining" tables in Google Sheets
I have two tables. The first one looks somewhat like this:
id name
----------
0 "abc"
1 "def"
2 "ghi"
3 "jkl"
4 "mno"
5 "pqr"
... and the second one looks like this:
id1 id2 foo
-------------
3 1 "a"
1 5 "b"
5 6…
Win32
- 149
- 1
- 9
1
vote
1 answer
Input text in one column (a) if the value in another column (b) is found in another column (c)
I have two lists of people - they will not be sorted in the same order. The second list is in a different sheet. If the person listed in column A shows up in Column A in the second sheet, I want column F to display "Y." If not, I want column F to…
Leila13543
- 37
- 2
1
vote
1 answer
Find and replace data from columns - Google Sheets
So I'm a complete noobie at anything slightly more complex spreadsheet wise but here's the issue. I'm looking to replace data in a range of cells (A) that matches data from a reference column (Y), with new data from the adjacent column with…
BetterCallSol
- 13
- 3
1
vote
1 answer
Merge multiple tabs in Google Sheets and add a column for where the data came from
I have a spreadsheet which contains multiple tabs with similar layouts. I want to use a formula to merge these into a single tab which has a new column naming the tab it came from.
Example
Tab: Area A
| Item | Status |
|------|-------------|
|…
superbriggs
- 629
- 1
- 11
- 19
1
vote
3 answers
Searching within the result of a vlookup using a range of values and parsing text
MY GOAL:
parse a MM/DD date from the result of a vlookup so that it can be used in a project plan
BACKGROUND:
The vlookup result contains multiple values separated by a "•" (I don't need all of them)
The value I'm looking to parse is not always in…
Kyle W
- 15
- 3
1
vote
1 answer
Is there a better way to group rows together with names from another column
Is there a faster way to do create the same results here. I am trying to group rows together based on the value in column K with a group name in column O. I want to add the Group Name in column O to a new column
=IFS((K1=$P$2), $O$2,(K2=$P$3), …
THRILLHOUSE
- 163
- 1
- 10
1
vote
1 answer
Counting the number of instances of a month in a list of dates in Google Sheets
I have a large spreadsheet containing job information. I am trying to make a "Job Count by Month" field, and it doesn't pull correctly.
My Job Info is all located in a sheet called "3PL Jobs". I have a hidden sheet titled "Under The Hood" which…
Jeff Gibson
- 428
- 2
- 9