Lookup Value In A List Within Part Of Text Value In Another List
Feb 21, 2012
I have two lists.
The first:
P1116
MC1070
BD1068
I want to check if any of these values are contained in the text of each item in the second list, that looks like this:
64K4014-4" , 64K1030-4"
64P1116-3"
64P1187-4" , 64p1117-4"
64P1330-2"
64P1405-2"
64MS1007-0.75", 64MC1070-2"
64K1062-3/4"
64K1061-0.75"
64CW1062-2", 64BD1652-2"
64NG1007-1 1/2"
64K1008-2", 64BD1041-1", 64BD1068-2"
64HS1002-6"
64HS1002-6"
64CW1068-2"
I tried using the =search() function in an array formula, but I can only get it to work for a single value in the first list.
View 8 Replies
ADVERTISEMENT
Feb 12, 2009
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
View 3 Replies
View Related
Aug 13, 2008
(1) I've created a list in A2, which looks up F1:F27. I'd like to place an IF or Vlookup function in B2, that returns the corresponding cell from range G1:G27 based on selection in A2.
(2) Also, is it possible to include the fill coulour based on selections in A2 and B2?
View 11 Replies
View Related
May 21, 2013
I'm trying to write a formula which will return postcodes from a list of descriptions which aren't consistent in their layout.
For example, I need this to happen
UB3 3NQ - APR13
SW3 5RQ - APR13
Jul 12 - apr13 accrual - ME9 4FW
Mar 12 - apr13 accural - SO14 7P2
Returned to another column as,
UB3 3NQ
SW3 5RQ
ME9 4FW
SO14 7P2
The issue I'm having is that the postcodes aren't in the same place in order to use LEFT, RIGHT or MID functions, and they aren't always proceeded or followed by dashes or spaces in the same way.
I need the returned postcodes to come back in a uniform way so that any duplicates are grouped by the relevant pivot table.
View 4 Replies
View Related
Jan 27, 2012
i have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
View 3 Replies
View Related
Sep 27, 2006
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
View 9 Replies
View Related
Oct 12, 2007
I am not sure if a vlookup can do the following.
I have a spreadsheet in excel with lots of rows. In column A I have some descriptions such as:
British Pound
Danish Krone
Japanese Yen
I have created another spreadsheet 2 columns that would have something like:
column A/columnB
British/Yes
Danish/No
Japananese/Yes
This 2nd spreadsheet can grow and have more lines.
In column Z of the 1st spreadsheet, I want a formula to look at the second spreadsheet and if any word in the column A of the 1st spreadsheet matches the word in column A of the second spreadsheet, I want it to the value in column B of the second spreadsheet.
View 4 Replies
View Related
May 8, 2014
I would like to be able to compare the "standards" in Column A with the Assessment status in Column B and the Assessment period in Column C in order to generate a compact list in another workbook (ideally) or tab (if not). A list might typically contain all the standards that have been M (mastered) during a given Assessment period. I realize that filters would achieve this to a large extent but I was hoping to automatically populate another worksheet or tab.
View 3 Replies
View Related
Jun 5, 2009
i want to do is, have a list that picks from a color and then beside it, it will show the possible fruit that is that color for example
Red
apple
cherry
strawberry
OR
Green
watermelon
grapes
The number of fruit will differ by the color so i know i need a counter there, but i am not sure how to list the fruit after i've picked the color
View 6 Replies
View Related
Jan 19, 2010
I have a list of about 200 parts that shipped. However, each part shipped several times over the last year. I probably have 3,000 - 4,000 lines total.
What is need is each part once, with the last shipped date.
View 9 Replies
View Related
Sep 28, 2013
Easy way to find the drop down list , if more than 500 list. at least the very first letter of the text.
View 1 Replies
View Related
Aug 5, 2014
how I can check if a cell contains part of a value from a list? Do I need a formula or VBA? (Both would suit)
Example:
A1= BLABLA Apple BLALBA
A2= BLABLABLA Orange BLA
List in C1:
C1= Orange
C2= Apple
If it contains one of the word, I need the function to return the word...
What I have tried:
Search formula
Match formula
VBA with "If...Like" statement
Keeping in mind that the key word Apple/Orange in A1 or A2 can be located anywhere in the "sentence" (could be after BLABLA or BLOBLOBLO or BLU etc...)
This is something I would then apply to a range (I suppose if it requires VBA, then with a loop which I would be able to manage)...
EDIT: Cells in A1/A2 will never contain two different values from the list (it is either Apple or Orange)...
View 6 Replies
View Related
Apr 27, 2009
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
View 3 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
Jan 3, 2014
I'm trying to create an autoupdating price list using a part number and a website. I've tried Excel's data import wizard.
Website: WebFLIS - Public Search
Sample data Category
Chaplain Kits
Item
Chaplain's Kit, Consumable
NIIN
9925-01-326-2855
Price
$276.94
Description
Also called the ReSuppply Kit
VBA Code that allows me to automatically open the page.
Dim IE As Object
Sub submitFeedback3()
Application.ScreenUpdating = False
Set IE = CreateObject("InternetExplorer.Application")
[Code] ......
Ideally, I'd like to click a button that says "Update Prices" and it will search WebFlis for the NIIN listed and update the price. I have 717 items on my list so updating would be by click only (I think I can write that portion).
If that is not an option, I'd like to be able to click on each item row (think hyperlink) and be able to see the results for that individual item.
View 9 Replies
View Related
Jul 15, 2009
In Column A i have a list of race meetings. Column B is the Country.
Column D - List of Tracks
Column E - Country Code the Track belong to
So what i like to do this this, i need to find out which Country a particlar Race Meeting belongs do using the Track List.
E.g. A2 - Ayr
In my Track List ayr = UK
So i need UK to be displayed on my B2 cell to state its from UK.
View 4 Replies
View Related
Jan 18, 2012
I have 3 sheets.
Sheet 1: A complete data set:Sheet 2: A partial data set (from sheet 1)Sheet 3: Empty tableI want to pull data from sheet 1 if it 'isn't' in sheet 2. Is there a way of tweaking vlookup for this purpose?
View 2 Replies
View Related
Dec 10, 2008
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
View 3 Replies
View Related
May 8, 2009
I have a price list Width/Drop
100013001600190022002500280031003400370040004300460049005200
850
1150
1450
1750
2050
2350
2650
2950
3250
3550
3850
I need to index given :-
My ranges are named Width and drop and List. Rules are anything above largest drop/width must return 0. anything below lowest width/drop will be the lowest listed
anything inbetween will choose the value >= to the value. lookup is returning the closest, and my other formula fell foul of nesting, i am working in 2007 but it is targeted at XP/2002.
View 5 Replies
View Related
Jul 2, 2009
I am creating a list with Data>Validation>Allow>list and it requests the source for the list- all OK, but the list produced needs to depend on other factors, I have several named range's containing different lists.
I need to insert in the "list Source" box a cell reference, which contains a the name of the range ie "list46" and for the source to recognise this as a list to produce
Is it possible to lookup a named range to produce a list?
Sorry about the explanation very difficult to put in to words!
View 5 Replies
View Related
Oct 27, 2007
OK trying to do a few things a once here.
First Validation list is D4:D9 @ Z10
I want this first validation to set for the next list, so if IRS was selected then the next validation will only look at Suite#1 or Suite# 2(C16:C17)
The third list I want auto created from the second list.
Example Suite# 1 picked from second list, so the third auto generated list would output Siemens(B16)....
View 9 Replies
View Related
Apr 19, 2006
I need to perform a lookup on the first row of a filtered list. i.e. If I search for VCP/3301/MP the filter displays all rows containing that part number. The first row after the header row may be say A320. The next time I search for a different code the first row may be at A120. How can I select this first row for my lookup?
View 7 Replies
View Related
Mar 18, 2013
I have been writing a speadsheet using drop down lists and the Lookup function to link names to body weights. When using the dropdown list it displays for one name the incorrect weight. All the rest work correctly but one will not. From the list below when I select the name White it displays '68' rather then '80' as it should. If I change the name of White to the number 4, it will act correctly and display the weight '80'. It seems rather bizare.
This is the function that I am using
=LOOKUP(C53,Weights!B2:C9, Weights!C2:C9)
Pilot
Jones
78
Smith
95
[Code] .....
View 5 Replies
View Related
Apr 14, 2014
How to write formula of following condition:
Input date on left column and the cell on right will automatically look up this date from the list located in other spreadsheet and return multiple corresponding in sequence order? Once another date is entered on left column, it will do the same function again on right column?
View 5 Replies
View Related
Oct 20, 2008
I have a list of account balances in one column. How can I pick up only the bottom number in that column? I have 20 worksheets and want a total across all worksheets of the last number listed in a particular column but each worksheet is different because they contain a different number of rows with activity. So, if in col. C I have:
$482
$1,000
$899
$456
$231
... then I want $231 to be what is captured for that worksheet, but if the 2nd worksheet contains:
$500
$1000
$344
... then I want $344 to be what is captured for worksheet 2. Does that make sense. In the end I want to sum $231, $344.... etc.
View 3 Replies
View Related
Jun 17, 2009
I need some assistance on creating a formula that will Match "W1 Sheet" Team number look this number under Schedule Sheet then list names under "W1 Sheet". I have added a simple sheet.
View 5 Replies
View Related
Apr 17, 2012
I am looking to do lookup list using the offset function. Scenario: I have 5 dealer groups, each with there own dealers. Each dealer has a list of customers. Once I select the customer it will bring up a list of their vehicles. I am stuck on selecting the customers and vehicles once the dealer group and dealer are selected.
View 1 Replies
View Related
Feb 13, 2008
i am trying to do a lookup on a data column (B) which contains time data on a worksheet which is a constantly growing list from a DDE data link macro- the data i want returned is in column A,
so for example
column A, column B
0.8970 13/02/2008 21:58
0.8965 13/02/2008 21:58
0.8990 13/02/2008 21:58
0.8995 13/02/2008 21:59
0.8982 13/02/2008 21:59
0.8983 13/02/2008 21:59
i am looking for the highest and lowest values where the time matches a certain hour of the day, so where another cell on the worksheet 1 equals 22, do a lookup on all the rows which is in time 22:00 and return the max no in column A (0.8983),
and the min in another cell(0.8977)
think my limited excel skills are not up to it i'm afraid despite searching through the forums and trying out a few lookup and max combos as array formulas.
View 9 Replies
View Related
Feb 20, 2008
I am working on a worksheet. I have the first tab at the bottom and that sheet contains all my data, and the additional tabs moving on the bottom are portions of that data for clarity. What i have on the primary sheet is 5 columns ( Name(A), Total(B), Tax(C), Amount(D), Date(E)) and being the master lists everything. I can add a filter to choose the name i want and naturally that only shows the relevant data for that name. What i want to do on the second tab along the bottom which is only relevant for the name i have chosen, their own sheet if you like is to be able to extract the data from the master sheet on a periodical basis. For example I have the same data that i need to show (including some additional stuff that manually include) and i will need to include Total, Tax, Amount and Date columns. What i want is to be able to have a drop down list in the cells below the Date column so that when i select the date it automatically completes the Total, Tax, Amount information that is shown on the primary sheet. So Total will be A column through to Date in D column and changing the date in column D will populate A to C with the data automatically.
View 9 Replies
View Related
Aug 28, 2013
I need to start a list in cell a8 on sheet1. I need it to find and list multiple results vertically. It will lookup what is in cell a1 on sheet1. The table of info is on sheet2 from a1 to b44. Column a on sheet2 has the values of what is in column a on sheet1 and column b is what I need returned to the cell with the formula.
View 3 Replies
View Related
