Using V Lookup To Search Data
Nov 3, 2009
I have looked at previous v lookup questions however was unable to do a comparison to the queries which I have. Hope someone is able to help. Sending spreadsheet to hopefully clarify
Sheet 1 = downloaded orders
Sheet 2 = present Customer database
Q1 - sheet 2 column E - can I make the address show without the return stroke (square symbol)?
Q2 - how do I return in sheet 1 column b and c the information held on sheet 2 column b and c. I have tried using the post code as the look up but it is only returning around a 30% find, can you use post code and rest of the address (post codes could be partially different as off 2 independant databases) to find a true match, or at least increase the 30% find considerably.
View 9 Replies
ADVERTISEMENT
Jul 17, 2008
=LOOKUP(2,1/SEARCH(0,G13:G1052),B13:B1052)
this will tell me the last 0,how do I find the first 0?
View 9 Replies
View Related
May 15, 2007
I am trying to use a lookup formula to search for a word in a column, and return the value of the column directly to its left. I know that this cannot be done using the vlookup function, and I am not able to move the columns around. Is there another possibility? In plain English then, I would like cell E2 to look up the value of D2 in column B2:B4 and return the relevant value in column A2:A4.
View 3 Replies
View Related
Jun 13, 2014
I'm currently trying to put together a search function of sort onto an excel document to look for entries of "Y" based on the choices in a drop down list to populate a list of names that have a "Y" next to them. I've searched the net and read up on IF, VLOOKUP and HLOOKUP commands but I can't quite string something together. An example of what I am trying to achieve is below;
Raw Data:
type1type2type3type4room1room2room3room4
AndyYNNNnYNY
BillYYNYYYYY
ChrisnYNYYYYY
DaveYNNNYYYY
EddyYNNNYYYY
FrankYYNYYNNN
GilesYYNYnYNY
HarveynYNNYNYN
IannYNYYYYY
JohnYYNYYYYN
KyleYYNYYYYY
Search drop downs:
Typetype1
Roomroom1
End result (based on the example of Type1 and Room1):
Bill
Dave
Eddy
Frank
John
Kyle
View 7 Replies
View Related
Feb 2, 2010
In the attached sheet I am trying to use the formula below but am getting a #NA error. I have narrowed the problem down to the use of the SEARCH and LEFT functions that I am using to determine the lookup value of the VLOOKUP formula.
what I am doing wrong? If I substitute the SEARCH and LEFT function with the number "14" it works just fine. You can find examples of both in cells B29 and C29 on the rename tab.
View 5 Replies
View Related
Feb 5, 2014
I have a 14 000-item list of product codes that are generated from different attributes. What I am trying to do, is to create a cover sheet where the user can select attributes from dropdown lists and get the first matching product code. I have tried using index/match, but I am struggling with wildcard lookup.
Example of generated product code:Cell A1:
ABX**J
Here, AB is the product group, X is the pressure class, ** is material (which is, for the example, unknown), and J is the end connection. What I now want to do, is to look in the long list of codes and find the first match:
Cells A2:A5:AAY02J
ABY03F
ABX01J
View 1 Replies
View Related
Apr 9, 2008
I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.
View 4 Replies
View Related
Sep 14, 2009
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
View 9 Replies
View Related
Sep 30, 2009
I have a spreadsheet!
I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).
What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.
Example:
Sheet 1 - Films
How To Apply Bridal Makeup
How To Fight A Donkey
How To Write Excel Formulae
Sheet 2 - Tags
Channel / Tag / Keywords
Tech - Microsoft - windows vista xp word excel
Tech - Computers - internet pc
Tech - MP3 Players - iPod Zune
So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
View 12 Replies
View Related
Jan 15, 2014
How to create a search formula that will look up a person's name in a given worksheet in a specified cell?
View 8 Replies
View Related
Feb 17, 2009
i want a macro to search all of column A in multiple sheets for a date that has been put into an input box.
the matching dates have data corresponding to them in that row. i want the date and data from the row to then be copied into a new sheet.
View 4 Replies
View Related
Jan 14, 2009
I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.
I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.
I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.
If value I5 says Period_1 it will only look in rows 12:46.
If value I5 says Period_2 it will only look in rows 48:82 etc.
I believe I need to modify the xl look part of the code with if statements.
Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.
Function Lookup_Occurence(To_find, Table_array As Range, _
Look_in_col As Long, Offset_col, Occurrence As Long, _
Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt
Dim lOcCheck As Long
View 5 Replies
View Related
Feb 12, 2014
I have a worksheet with following values:
A
B
C
1
Shorts
75
[Code]...
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
View 1 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Nov 28, 2006
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
View 3 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 Replies
View Related
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Aug 4, 2009
I am attempting to do a search on a 2 dimensional table, based on Product Code/Month. Is there a function, addin or a macro that will allow me to do this? All I can come up with is a messy combination of VLOOKUP/HLOOKUP/COLUMN.
View 2 Replies
View Related
Mar 28, 2014
I have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
View 9 Replies
View Related
Mar 7, 2008
I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14
51 86 REP 1 33.3% 14
42 74 REP 3 19.3% 11
57 86 REP 5 19.6% 9
46 66 REP 4 33.3% 9 ...........................
View 9 Replies
View Related
Nov 9, 2013
see sample file, i need a formula to do like B column,
I WANT MAKE LIKE B COLUMN, COMPARE A COLUMN DATA IN C,D COLUMN AND ALL DATA SHOULD BE IN B COLUMN..
View 7 Replies
View Related
Mar 27, 2007
I have a excel spreadsheet that has employee names, email address and DOB. The DOB format is 23/03. I need a program that will lookup the DOB row and when it matches the value I enter it extracts the employee name and email address. Once it has this information I need it to copy into an already created email template and send its off. On some days there are more than 1 person with a birthday so it will need to send multiple emails.
View 7 Replies
View Related
Mar 23, 2009
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
View 2 Replies
View Related
Jul 29, 2012
i have a treatment centre with 22 rooms some with mutiple beds, i need to view occupancy in a grid form to optimise bed useage. i need to look up a date that is after arrival but before depature that matches a room number, i have tried match, index, and, vlookup etc but all this info must exist on the same row
View 1 Replies
View Related
Jun 9, 2009
I'm kind of rusty with spreadsheets and Excel 2007 is entirely new to me. I'm not even sure what I'm trying to do would be called.
I have a spreadsheet that is a list of records; a name, ID number, one text, and four numeric columns per record.
I would like to make a set of buttons or something that will automatically do a custom sort. Basically a "sort by this criteria, sort by different criteria" etc. so I don't have to manually do the sort repeatedly.
View 9 Replies
View Related
Dec 30, 2009
i want to use for searching a name in a colum. And copy the row of this name to another row.
I want to use this because i want to change an format to one i use all the time
person Astreet awork a
person Astreet bwork b
person bstreet cwork c
This is the situation: i want to search for person A and copy the data of the row , so copy street a. and work a. to another row
And i want to do the same for person b and so on until person z
View 14 Replies
View Related
Oct 1, 2008
I am trying to find a way to have a cell look into a group of other cells and display the first available things it comes to. Then have the next cell look in that same group and display the next item.
cells A1:A5 have 3 pieces of information in them scattered among that column (A1, A3, A5 might have the info in it one day, then A2, A3, A4 the next day)
I want B1:b4 to find the info in the A1:a5 and display it in order as it appears in the A column.
View 9 Replies
View Related
Feb 16, 2012
I have a formula that looks in 2 columns for criteria and then does a count if both sets are matched.
=SUMPRODUCT(--(data!$AC$1:$AC$10000="Y"),--(data!$AV$1:$AV$10000='Summary Sheet'!B9))
Is it possible to do a search with 3 criteria?
I need a search where a third column has also to match. eg data!$AW$1@$AW$10000="Y"
Is this possible and if so what would my formula now look like?
View 1 Replies
View Related
Jan 17, 2013
I have a spreadsheet with multiple coulmns of data with rows which equate to each site location.
Basically I am after searching one column which says "Earth Rods Fitted" which is located in Column K which goes from K4:K767
In each the rows its either yes or no answer.
So the search would count number of "no" entries in column K.
But then it would search also on column Y (Y4:Y767) for a range of values of risk assessment.
So in column Y you could have each row with different assessment risk score from 0-300
So my search would need to count coloumn K for no earth rods fitted and then count within this range the number of cells in column Y which have risk score say between 200 to 400.
Column K Coulumn y
Earth Rods Fitted Risk Score
No 350
Yes 55
No 222
No 90
So in above case there is 3 enries with no earth rods in column K and in Column Y we are counting rows which have a value in range of 200-400 which above there is 2 entries. So basically I know there is 3 sites needed doing and my worst to based on risk is the two which are 350 and 222.
I have messed around with COUNTIF functions which I can search column K ok, but doing the range search on y in conjuction with K I am finding hard. Someone mentioned use Vlookup but not sure how to do it.
View 9 Replies
View Related
Jan 10, 2008
i have is that at current i have a load of data (6000 cells worth)
What i need to do is to go through the data and highlight anywhere where it may have the word "in/outsourcing" (The data is based in cell AA which is just a description of text)
Other than doing Ctrl F through each cell is there a faster way in which excel can search through the cells and even highlighting the cell where the word occurs.
I thought if the word "sourc" is searched this would then pick up both
View 9 Replies
View Related
