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’).
166
questions
0
votes
0answers
5 views
VLOOKUP (PROCV) It's not returning the same value in the next line!
sorry I've been over this, but I have a specific "ORDER NUMBER" that shows up around 10 times with different "MONEY VALUE", so I did a dynamic table to group up all the "MONEY ...
0
votes
1answer
24 views
Using VLOOKUP with pivot table - OR - another way to aggregate data
My sample is here:
https://docs.google.com/spreadsheets/d/1j9pRECnAKtKtvAfhMvLv93-u6_kwIrFZg3RY3jSkpkU/edit?usp=sharing
I am trying to aggregate the last date that each "familyID" was ...
1
vote
1answer
35 views
Google Sheets: VLOOKUP formula for a multi line cells range
I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET.
In the RECORDS I have a list of names, in alphabetical order, and a ...
0
votes
1answer
15 views
Find given time and date in named range
I have a Google Sheet with my wife where we both enter our upcoming events. They are included in named ranges and passed to the main sheet where the information is displayed. For example, I enter in ...
0
votes
0answers
19 views
Query - Procv with 2 Conditions
I'm trying to do a query with 2 conditions:
=ArrayFormula({"User"\A1:F1;QUERY(SE.ERRO({PROCV(B2:B&F2:F;G2:H;2;FALSO)\A2:F});"Select * Where Col1 Is Not Null Order By Col1 Asc")}...
1
vote
0answers
19 views
How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?
I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
0
votes
1answer
46 views
Look up rate for specific person in effect on or after a date
In this Google Sheet, I have one sheet, "Transactions", with a rate entry that should be auto populated from a table in the "Rates" sheet. The formula should look up the rate for ...
2
votes
1answer
34 views
Searching cells and pulling values in Google Sheets?
sorry for the vague title but I'm not sure how exactly to describe this.
I've created a spreadsheet and used the Coinmarketcap API to pull lots of data, here's the relevant part:
From another sheet I ...
0
votes
1answer
15 views
Why does VLOOKUP find a match in a sequence of dates when there is none?
I am working on spreadsheet that works with a school calendar. I have a sequence of dates that is a list of Wednesdays. I am trying to do a VLOOKUP on it to find out what type of day a specific date ...
0
votes
1answer
42 views
Copy data based on date to another cell but other column date is not exact match
I want to match the dates in Column A to the Closest date in Column E (if a date in column A doesn't match to dates in Column E then I want to return the next available date in Column E and I've done ...
2
votes
2answers
337 views
Categorize text in Google Sheets based on matching partial text in the Category index
I'm looking to match, with VLOOKUP, part of the words in a column in the main sheet to an index of keywords/categories where partial words are stored for ease in another sheet.
Here's an example ...
1
vote
1answer
109 views
Search multiple columns for one value
This is roughly what my table looks like:
A B C D E F
Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
0
votes
1answer
26 views
How to assign country based off list of either provinces or states?
I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations.
I also have a list of ...
1
vote
1answer
204 views
Sheets - Lookup most recent entry based on unsorted date value
Spreadsheet Im working on: https://docs.google.com/spreadsheets/d/1rTzdmKVMqD8sz1D639y5qgvXNBcWaAuBaQOAmwJXdA4/edit#gid=0
I need a formula that returns the Comment for the Domain that equals the ...
0
votes
0answers
24 views
Go into “Display” tab, click a name off the drop down list, and view that individual's data
I have 3 tabs. "Timestamps" "Techs/Locations" and "Display."
My form timestamps when an employee clocks in or out. I have created two separate columns to carry over the ...
0
votes
1answer
24 views
How to replace or substitute one column of data with their corresponding value from a list
I'm trying to replace a column of data (Prodct SKU) on one sheet (a) with the corresponding value taken from 2 columns on sheet (b) (Product SKU & Product ID).
I've attached the spreadsheet to ...
1
vote
1answer
38 views
Using a query and tick boxes to auto fill another tab
I'm new to this and I'm racking my brains over how to get this to work, I can do basic IF & Vlookup but I believe I need the merge the 2 for this one.
Is there a formula I can use on the "NLN" ...
2
votes
1answer
81 views
How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D
I'd like to populate Columns C and D in an entirely new spreadsheet, where Column D still auto-populates the cells just as it does in the original/first sheet (as it does in the photo attached inside ...
1
vote
1answer
57 views
Formula to find the cell in column C where a value in column A = a value in column E
I'm trying to create a formula in a google sheet to find the value in the C column where the value in the A column is equal to the value in the E column.
E.g. Column E = 1 in Row 9-16 and Column A = ...
1
vote
1answer
224 views
Google Sheets Inverse (or Reverse) LOOKUP … is this possible?
As far as I can tell, all LOOKUP functions require you to know a specific search_key (like a column or row header) in order to search for and retrieve a relative value.
Inversely, is there a way to ...
1
vote
2answers
229 views
Adding 1 to a Cell with VLookup
So this is my first time trying to write a script, and the sheet I am working on is here.
The script I'm trying to run would allow me to push a button and have it use Vlookup to add 1 to a cell in ...
0
votes
1answer
30 views
Indirect formula to add table field to vlookup formula
So I have to get a price based 3 variables
Diamond size
Diamond quality
Diamond Shape
So I made tables depending on shapes
This formula is working
Diamond size = H11 = 1
Diamond quality = $K$3 =...
1
vote
2answers
239 views
Array formula with index match to return array of matches
I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA.
What I'm attempting to do is match a ...
1
vote
1answer
48 views
Help Sorting Data
I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link:
https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=...
1
vote
1answer
343 views
Google Sheets calculate average on variable number of non-adjacent columns?
A Google Sheet is being used to insert markings for students tests.
When a single test is done by students, teacher assigns multiple grades for it. For instance, one marking for writing, one for ...
2
votes
1answer
60 views
How to find, out of many days, 1 day that is most profitable on Google Sheets?
Ok, I've been looking for this type of formula but not finding it. So here I am asking for help.
I'm a day trader on stocks and I want to analyze my performance. For example:
+-----------------+-----...
1
vote
2answers
6k views
“Error Did not find value '____' in VLOOKUP evaluation.”
Please see my public spreadsheet here:
https://docs.google.com/spreadsheets/d/11RMN7uN_woEQVzlDEkX6vSARTCGYYBjWs11ZDU-6Shc/edit#gid=1276951687
My goal is to get the Location Code from tab 1 into tab ...
1
vote
1answer
682 views
Google Sheets - If cell contains text that matches text from a column, return another cell
https://docs.google.com/spreadsheets/d/1GG7ekrD6CxxpowrRDm9DMvv3xHdvtAa8MHYCk4gI4D4/copy
1) If D10 (or I10 or N10) matches an exercise from the corresponding column to the right, return another cell (...
1
vote
1answer
28 views
Make cell range appear in different position to VLOOKUP?
I want to use VLOOKUP to search the highest value in a given range, and report to me what day it's the highest.
Example VLOOKUP for my actual data set in case if it matters:
=VLOOKUP(MAX(A3:A1001),...
2
votes
1answer
67 views
How to automatically calculate averages from ID in Google Sheet?
I've been looking around for an answer but couldn't find what I was looking for.
The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that:
Looks at the unique ...
2
votes
1answer
1k views
How to check if the values of two cells exist in two columns of another sheet
I am trying to make a class attendance system where the student checks in by submitting her name into google forms. The final result will be displayed in Google sheets by marking a check box after ...
1
vote
1answer
107 views
Google sheets Xlookup dont work but need results in table the max date on the condition of userlist
Need some help with some data processing.
Link of the sheet
Example - I need on :
Page-2-Seznam članov > Coloumn C : It needs to be the max dates.
The function need to search on the liste the ...
0
votes
2answers
3k views
In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action
In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
1
vote
1answer
157 views
Filter the row containing an exact word and highest value in Google Sheets
I have a table (Master sheet) in Google Sheets that looks something like this:
https://ethercalc.org/t8f0ze0kyx6a
I need to filter out an entire row of information on a separate sheet (Last Lesson) ...
0
votes
2answers
88 views
How do I “bin” results in Google Sheets?
I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale.
For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
1
vote
2answers
32 views
Searching a list and replacing value if any item matches any item in the other list in Google Sheets
I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
-1
votes
2answers
16 views
Value of Cell A1 could point to values of different cells
I am having difficulty with the following. Cell A1 could have values from 1 to 50. I have a listing on a separate table of different values that 1 to 50 represents. (I.E. 1 equals 1.68) I would like ...
-1
votes
3answers
33 views
How do I sort data by size and not in ABC order
I am new to Google Sheets and have only used Excel a little, so Google Sheets is a bit different. I want to sort our band uniforms in size order from smallest to largest but Google Sheets only sorts ...
0
votes
1answer
55 views
How do I add values from one sheet based on a dropdown that references the other?
I have 2 sheets with the same columns with integer values. In sheet A I have a drop down list of the names in sheet B. Based on what drop down I have selected, I'd like to add the values of sheet B to ...
1
vote
0answers
90 views
Arrayformula with iferror and vlookup does not update automatically
My spreadsheet is for inventory checking. I use several of arrayformula ,iferror and vlookup.
The problem is I just noticed that sometimes the formula does not update. It might because I duplicated ...
0
votes
1answer
324 views
Having trouble outputting GOOGLEFINANCE data
My wish is to create a table in Google sheets looking something like this:
price
day A B C D ... Z
1997-01-02 132.54 . . . .
1997-01-...
1
vote
1answer
56 views
Get last value entered past a specific row
I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has ...
1
vote
1answer
41 views
Returning max value based on two conditions
I've been reading through a lot of other solutions to similar problems but am not able to resolve my issues.
I have a Google Sheet with two sheets I am working with:
Sheet 1
A list of contacts (...
0
votes
2answers
25 views
Query twice in the same cell to receive a name next to a number in a row
Sheet link
I posted before however, I don't think I was specific enough. I am sorry if I am wasting the time of anyone.
In my sheet, you will see three pages. On the "Search Notes" page in C5 I ...
1
vote
1answer
1k views
GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low
I'm using Google Finance calls in Google Sheets for some calculations.
I've got 2 formulas, one to find the low price, and one to find the high price from a certain date:
=MIN(INDEX(GOOGLEFINANCE("...
1
vote
1answer
42 views
Query sum for unique pairs of rows
I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example.
City Person Days
London Tom ...
-1
votes
2answers
51 views
Help with a Vlookup function to return multiple results of not exact match
I am trying to run the following code to return all the matches of column A from column C in a set of rows. In this situation, cell A1, is contained in a few iterations of column C, however, these are ...
0
votes
1answer
32 views
Assigning a value to a name from a list
I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0
votes
1answer
69 views
Can I populate Google Sheets cells adjacent to a target cell?
I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
1
vote
1answer
39 views
How to search value in another column?
Using Google Sheets, I am trying to two compare two columns on Google Sheets, like the one below:
Basically, I want to compare column A and B, basically, if a cell in column A is inside a cell in ...
