VLookup To Compare Two String Values And Return Value Of A Cell

Oct 22, 2012

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.


IF Otherworkbook.sheet1.A20 = A1 return the value of B20

View 6 Replies


Compare A String Of Values And Return The Header

Dec 19, 2013

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 Related

Vlookup Return Multiple Values In One Cell

Feb 15, 2010

I 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.

View 7 Replies View Related

VLookup One Value And Return Multiple Corresponding Values In One Cell?

Feb 23, 2012

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

View 9 Replies View Related

VLookup To Return Multiple Values In Same Cell?

Jul 17, 2012

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



View 6 Replies View Related

VLookup Multiple Values And Return In One Cell With Comma Separated

Apr 6, 2013

lookup Range1
lookup Range2
Return Value
Multiple Return


I am trying to vlookup multiple value and return multiple values in one cell

View 7 Replies View Related

Vlookup To Return Second Occurrence Of String

Mar 14, 2007

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

HOME 20 6 1 0
AWAY 20 7 2 0
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:

HOME 20 6 1 0 2 0 1
AWAY 20 7 2 0 3 1 4

View 2 Replies View Related

Using VLOOKUP To Compare Two Values

Jun 23, 2013

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


Sheet2 shows the 'SKU', 'Name' and 'Option1'. Option1 contains the values I'm trying to compare... sku":"X-MiniB-S","value":"S","quantity":"1"


Basically, I need to check that 'Current Stock' matches the value in 'Options1' and return a value if they do not match.

View 9 Replies View Related

Using VLOOKUP To Compare Values Without Unique ID?

Mar 18, 2013

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


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. .

View 9 Replies View Related

Compare Cells Values And Return Value?

Nov 27, 2012

I'll quote the example straight away.

Col A Col B
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)

View 7 Replies View Related

Double VLOOKUP: Compare TWO Values In TWO Cells

Sep 25, 2008

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?

View 9 Replies View Related

Compare Two Values In Two Adjacent Cells Using Vlookup

Mar 4, 2010


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.

View 9 Replies View Related

Compare Values In Multiple Columns And Return A Result

Mar 17, 2009

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.

View 7 Replies View Related

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

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.

View 9 Replies View Related

Compare Multiple Text Cells To One String Of Text And Return Specified Value

May 12, 2012

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?

View 1 Replies View Related

Macro To Compare Values In User Specified Columns And Return To Different Workbook

Feb 21, 2014

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:

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.

View 1 Replies View Related

Return Dates As String In Values Only

Jun 24, 2013

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.

View 13 Replies View Related

Way To Return Column String Values

Feb 11, 2008

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.

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?

View 9 Replies View Related

Look For Two Values And Return A Text String

Jul 13, 2009

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?

View 9 Replies View Related

VLOOKUP To Find Values In Text String?

Feb 17, 2010

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.

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.

View 6 Replies View Related

VLookup To Return Yes And No Values

Aug 5, 2009

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.

View 9 Replies View Related

VLookup Won't Return Any Values

Jul 25, 2012

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 2


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:



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?

View 9 Replies View Related

Vlookup & Return All Values 2

Jul 7, 2009

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 ............................

View 9 Replies View Related

Vlookup Two Values And Return Another One

Oct 16, 2009

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.

View 9 Replies View Related

Compare String Of Words To Cell Content

Jan 31, 2008

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
i = InStr(i + 1, List, ",")
m(n) = i ..........................

View 5 Replies View Related

Vlookup To Return Multiple Values

Jul 6, 2007

Is there a way where i can vlookup a column and return all matches if there are multiple values?

View 5 Replies View Related

VLOOKUP To Return Multiple Values?

Jun 27, 2014

I have a list of countries in column A, there are duplicates in this list. In column B are numbers corresponding to the country.

USA 3443

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"

View 3 Replies View Related

Vlookup And Return Multiple Values?

Jul 22, 2014

formula to vlookup and return multiple value in a single as per the attachment.

View 7 Replies View Related

Use A VLookup To Return Multiple Values

Apr 7, 2009

I 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 Related

VLOOKUP To Return Multiple Values

Jun 3, 2008

I'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

Copyrights 2005-15 www.BigResource.com, All rights reserved