I need to compare a string from say A1 to a range of cells in another workbook and if found return the value of a cell on the same row.
Something like - look up string value in A1 in other workbook.sheet1.A1:A65535 if matched return the value of matched row column b.
OR
IF Otherworkbook.sheet1.A20 = A1 return the value of B20
Basically I have a string of dates, I want to get the max of the values that are before a specific date and return the header. Look at the file I provide for better understanding.
View 2 Replies View RelatedI tried searching for a solution to my formula problem in this forum regarding vlookup and saw similar formula e.i returning multiple values but it doesn't cater with what I need.
Vlook is returning only the first value but what I need is to return all the multiple return values in 1 cell. I've attached a sample worksheet for reference.
OK for example if i hae this list of data:
Invoice number Order number
111 560
112 561
112 562
113 563
What i need to do is a vlookup from the above table to return the following:
Invoice Number Order number
111 560
112 561, 562
113 563
Traditionally the vookup will return the first value it can match. I can return multiple values on different lines using an array formula but this is not useful.
In the example below I have table array A1:B8. Lookup criteria in cell A11 and want to return MULTIPLE values from column B in cell B11.
I would prefer to not use VBA if possible
A
B
1
[Code]....
code1
code2
lookup Range1
lookup Range2
Return Value
Lookup
Multiple Return
[Code]....
I am trying to vlookup multiple value and return multiple values in one cell
I have multiple automatically updating data sets and I need to extract certain data to perform calculations on, this is the format of the data
SPLIT AB H 2B 3B
HOME 20 6 1 0
AWAY 20 7 2 0
SPLIT R HR RBI
HOME 2 0 1
AWAY 3 1 4
basically, what I need is to extract the data for home and away, and put them each on one line, no problem if this is always the format, but there are other lines of data that I don't need thrown in there at times which shift the data up or down
a simple vlookup will extract the first occurance of home, but I need another formula to extract the second occurance so I can have them all on one line and have the data as:
SPLIT AB H 2B 3B R HR RBI
HOME 20 6 1 0 2 0 1
AWAY 20 7 2 0 3 1 4
I'm trying to compare two quantities on two different sheets. I've used VLOOKUP in the past on other sheets. This time round I cannot understand how to us it on this query when the data is contained and mixed in one cell.
Here goes explaining..
Sheet1, shows we have stock of Mini Speakers in blue, the 'Main SKU', 'Size' and 'Current Stock'
'Current Stock' is the cell we are trying to compare
Sheet1.PNG
Sheet2 shows the 'SKU', 'Name' and 'Option1'. Option1 contains the values I'm trying to compare... sku":"X-MiniB-S","value":"S","quantity":"1"
Sheet2.jpg
Basically, I need to check that 'Current Stock' matches the value in 'Options1' and return a value if they do not match.
I have a somewhat convoluted problem. I am trying to develop a QA tool that compares data from a user-created spreadsheet against data from an automated spreadsheet. From my (admittedly small) understanding of Excel, this would be easy to do using VLOOKUP, however there will not be any unique IDs between two columns.
To better visualize this, imagine on Sheet A the following:
Placement Name
Ad Tag
Clickthrough URL
[Code]....
Essentially what I am trying to do compare data from a manually filled in sheet against data that would output the above, and make sure that everything matches up correctly. .
I'll quote the example straight away.
Col A Col B
A,S,F A,S
A,R,S A,R,S,T and so on.
There is no fixed pattern for the values, but they are separated by commas only. All i need is to compare col A and col B and return me the value in Col C like this,
Col C (col C is to know the removed value from comparison)
F (for line 1)
Col D (to find the letters that are added from comparison)
T (for line 2)
I'm curious if there's a way to use the Vlookup function to compare TWO values in TWO cells with two values in two OTHER cells (same row though) and return one value from another column. Something like
Vlookup(A2 & C2, Sheet2!A1:C400, 3, FALSE & FALSE)....? Does that make sense?
Say the values in A2 and C2 are in A24 and B24 respectively in Sheet2. Can it return C24 for me?
jane....kaser...highschool......A
jack....Anderson....college.....B
Jill....Lovin.....college.......C
if both jack+anderson in a same row adjacent cells
as per in my table it should return column 4 value (A)
can I use VLOOKUP?
is there another way ?
I want to search a block of two adjacent cells in a table and return its forth column value.
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.
I have a column between the years that calculates the percent of gross revenue for the specific department/cost.
I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.
The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
I am trying to create a macro that will compare several un-formatted workbooks and copy just what I want to a workbook of my one.
I start by having 2 different workbooks opened ("Main" and "Change") side by side like this: 1.jpg
Then i run the macro in my "Main" workbook and it should appear an Userform like it showed in the image below: 2.jpg
This is so that i can manually fill the user form with the correct columns by looking at the excel file, for example:
Ref=E3;City=G3; Data=I3.
Pressing Ok, it fill the "Main" workbook with the matching information so it and closes the "Change" workbook.
At the moment my code is in this stage:
VB:
Private Sub CommandButton1_Click()
myfile = "C:Userssst1brgDesktopRelatorios" & TextBox1.Value
Application.Workbooks.Open Filename:=myfile
Windows.Arrange ArrangeStyle:=xlVertical
[Code] .....
But I am stuck because there isn't any error and nothing happens.
Formula that will return a string of dates (in values so that I use the cell for additional formula).
For example:
A1 B1 C1:to whatever cell needed, to be autofilled with day, date, month, year, weekdays only.
Start Date 2013/07/04 Monday, July 1, 2013
It would be really nice if it highlights the local holidays too.
I have made this function that returns the string value for a column based on it's number but it starts getting more complicated after I've been thru the alphabet once and have to return two string values to move across the chart.
PHP
Function colnum_string(ByVal num As Integer) As String colnum_string = "IV" If num = 1 Then colnum_string = "A" End If If num = 2 Then colnum_string = "B" End If If num = 3 Then colnum_string = "C" End If If num = 4 Then colnum_string = "D" End If If num = 5 Then colnum_string = "E" End If If num = 6 Then colnum_string = "F" End If If num = 7 Then colnum_string = "G" End If If num = 8 Then colnum_string = "H" End If If num = 9 Then colnum_string = "I" End If If num = 10 Then colnum_string = "J" End If If num = 11 Then colnum_string = "K" End If If num = 12 Then colnum_string = "L" End If If num = 13 Then colnum_string = "M" End If If num = 14 Then colnum_string = "N" End If If num = 15 Then colnum_string = "O" End If If num = 16 Then colnum_string = "P" End If If num = 17 Then colnum_string = "Q" End If If num = 18 Then colnum_string = "R" End If If num = 19 Then colnum_string = "S" End If If num = 20 Then colnum_string = "T" End If If num = 21 Then colnum_string = "U" End If If num = 22 Then colnum_string = "V" End If If num = 23 Then colnum_string = "W" End If If num = 24 Then colnum_string = "X" End If If num = 25 Then colnum_string = "Y" End If If num = 26 Then colnum_string = "Z" End IfEnd Function
Is there a better, more simple way to return the string value for a column from it's number reference?
Say I have a table like this:
Value 1---Value 2---Name
1---------1---------Potential client
1---------3---------Loyal client
2---------5---------Happy client
Now I have two separate columns with values for "Value 1" and Value 2". How can I tell Excel to look in the table above and return the name?
I need to to highlight keywords within a block of text (string). I don't need to necessarily return a value with the VLOOKUP. I just need to compare keyword list in Column B to the Text in Cell A1. If the word from the list appears in the text, I need it to be highlighted for every instance.
EXAMPLE
Text String (A1)
Welcome to Jake's Car Accessories website. We have all of your car accessory needs including car covers, truck covers, custom car grills and other car accessories.
Keyword List (Column B)
car accessory
car covers
truck covers
car accessories
I think VLOOKUP only works on lists.
Is it possible to write a vlookup that will return a value of Yes if the value is true and a value of No if the value is false?
I have data in column A on sheet 1 so the lookup value starts in cell A2 and the table array is on sheet 2 in columns A and B so the column index is 2.
When I use my vlookup function and hit enter, the no value shows up and I can still see the function syntax that I wrote in the cell. Here's a sample of what my sheet looks like:
Number
Name
Hazard
Number 2
[Code]...
I need to know the value in the 'number 3' column is for the 'number' column. For example, I need '1302-76-7' inputted in a column adjacent to one of the first three columns. Another example: I need 'na - 07' inputted in the same row that has a 'number' of '1073'. This is how I want it to look:
Number
Name
Hazard
Number3
[Code]...
When I type in the vlookup formula listed above, nothing happens. This is exactly how it looks in my spreadsheet, even when I click another cell or hit enter. Is my syntax wrong or have I just missed something?
Very similar to the original thread 'vlookup & return all values', i need to lookup a velue and return all the relevant values.
Unlike the original thread, i need the macro to return an array of cells, rather than just one.
The macro from the original post was this
Public Function FindSeries(TRange As Range, MatchWith As String)
For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", "
End If
Next cell
FindSeries = Left(x, (Len(x) - 2))
End Function ............................
I have two workbooks which are data dumps from another program. Say I have WO #, Task #, and Cost in workbook1 and WO #, Task # in workbook2. I need to compare the WO# in workbook2 with WO# in workbook1 if it matches I need to compare the Task # in workbook2 with the Task# in workbook1 (but it must compare the same row in which the WO # match was found) then it must return the cost from workbook1 to workbook2 ( again the cost must come from the same row in which the WO and Task match were found in).
This could be a VBA code or even a formula that does not matter.
I need to compare a list of keywords (stored in a string variable) with the content of a cell in order to see if any of the keywords do appear in the cell. I have to work with many such lists which can grow over time. What I'm doing is using InStr(cell with content, keyword) for each keyword. My goal is to have a macro which can automatically create the long formula from any list. My actual problem is that the formula I've build and stored acts more like a string. I'd like it to say either true or false. Anyway, here's a sample of what I came up with so far.
Sub build_formula()
Dim i As Integer, j As Integer, m(20) As Integer, n As Integer, z As Integer
Dim List As String, f(2) As String, s(20) As String, sp As String, Source As String
Dim Formula As Variant
List = "black, blue, green, red, yellow, white, z"
Source = Cells(3, 1).Value
z = InStr(1, List, "z")
i = 1
n = 1
Do
i = InStr(i + 1, List, ",")
m(n) = i ..........................
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 5 Replies View RelatedI have a list of countries in column A, there are duplicates in this list. In column B are numbers corresponding to the country.
A B
IRELAND 1221
GERMANY 2341
USA 3443
IRELAND 2231
Ireland could be in the list and have up to 20 different values beside it in column B
On a separate sheet i have a drop down list of all the countries. I am looking for a vlookup or something similar to return a list of all the numbers beside IRELAND when Ireland is selected in the drop down list.
I had been trying a vlookup but this looks at the list and stops at the first "IRELAND" it finds and just returns the value beside the first "IRELAND"
formula to vlookup and return multiple value in a single as per the attachment.
View 7 Replies View RelatedI have a workbook with 2 sheets. The sheet called 'data' contains all purchasers names (which do get repeated), the item they bought and 3 item costs, all in range A1:E15. The 'lookup' sheet allows the user to enter a purchasers name in cell B1, then I want all the items that purchaser bought listed with the corresponding item costs. I've attached a sample spreadsheet.
View 3 Replies View RelatedI'm having trouble getting my payroll data to combine. I've been using VLOOKUP to look up employees by their Personel Number (Column A) and return their name (column B), Cost Center (Column C), the Fund that they are paid from (Column D) and the Amount they are paid (Column E). The problem is that some employees are being paid out of 2 or more funds and VLOOKUP is only returning the fund that appears first, leaving out the other portion of their pay and thus making my grand total quite far off. How can I get Excel to return all the entries for a particular Personel Number? There are more than 16000 employees, so this is no small spreadsheet.
View 14 Replies View Related
