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
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.
View 9 Replies
ADVERTISEMENT
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
[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. .
View 9 Replies
View Related
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
Mar 4, 2010
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.
View 9 Replies
View Related
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.
OR
IF Otherworkbook.sheet1.A20 = A1 return the value of B20
View 6 Replies
View Related
Feb 21, 2014
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx
View 3 Replies
View Related
Oct 26, 2009
I need a Vlookup to look at two columns when searching. here is an example:
Sheet 1 is my report grid to hand out to the employees, A1 has a drop-down list with my employees names and A2 one for the month, when you click an employee name the vlookup formula fills in the information from sheet 2 (data sheet).
Sheet 2 has data like: Col 1= Month, col 2=employee L-name, col 3= production etc...
currently the vlookup formula messes up because the employee name is in there multiple times because of the months column, i have to delete all months except the one i need. formula is: =vlookup(A1,'sheet2'!,B:C,2,false)
this formula will pull the data in sheet two column 3 (production) for the employee that matches.
how can i make it compare first to col 1 (month) and then col 2 (employee).
View 10 Replies
View Related
Jan 8, 2010
comparing cell data 1 from data 2 but what I need to extract are only the numbers and using a vlookup.
Data from 1 Data from 2 DN000254162 GSODN000254162 IN0513231 GSOIN0513231 CI/0003216535/BAH CI/3216535/BAH
View 9 Replies
View Related
Apr 23, 2009
SHEET1 Cell A1 HAS EIGHT CHARACTERS THAT MATCH THE FIRST 6 CHARACTERS IN A1 OF SHEET2. BUT, SHEET1 CELL A1 THE FIRST CELL HAS 8 OR MORE CHARACTERS.
HOW DO I GET A VLOOKUP TO COMPARE TO FIRST 6 CHARACTERS OF SHEET1 TO FIRST 6 IN SHEET 2? In columm B sheet1 i would like the result of column c sheet to, e.g. 9
E.G.
SHEET1
A B C
DOGGIEDO
SHEET2
A B C
DOGGIE 5 9
View 3 Replies
View Related
Oct 26, 2012
I'm having difficulties using vlookup to compare two columns of TEXT data. My goal is find out how many of items in the "Eligible Serial Numbers" list have been ordered for destruction. The items listed for destruction are listed in the "Serial Numbers (destroyed).
Whenever I put in a VLOOKUP, I get a serial number that doesn't make sense to me. For example, if I want to find out if "362351581" from the Eligible List appears on the destroyed list. What do you suggest?
Code:
Destroyed? (i.e. Vlookup formula in this column)
Eligible Serial Numbers
Serial Numbers (destroyed)
362351581
362351581
[Code]....
View 9 Replies
View Related
Feb 11, 2013
compare some list of values in order to fiind the common values.
View 1 Replies
View Related
Jun 5, 2012
I have .csv file from which the data is importing to master schedule. i have a column in .csv file which is spitted into multiple columns. and my need is i have to compare this each individual cell value with the range of header values in master schedule and if match found i have to place that cell in the row.
I am adding 2 attachments one is .csv and other is master schedule.
I couldn't able to find where i have to attach my files.
View 2 Replies
View Related
Sep 25, 2013
In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).
Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.
View 1 Replies
View Related
Jul 24, 2014
I'm trying to compare two very large files to find some that are in one AND also in the other. I'm using =VLOOKUP(C2,Parts2!C:D,1,FALSE) and my results are #N/A for all of them. I have formatted both columns as 'General' because there can be letters and numbers in the serial number, I have also tried formatting as text to no avail. I have confirmed no preceding or trailing spaces or other special characters.
I am however, able to locate values in both documents using Ctrl F. I'm totally puzzled because I've always been able to figure out what's causing my Vlookup issues when I have them but this time I'm not able to.
View 14 Replies
View Related
Sep 1, 2013
I am trying to compare two lists and extract from one to add back to the source. However neither are alphabetically.
List 1
accessories
belt
boots
cardigan
coat
glasses
gloves
hat
List 2
tshirt
tshirt
jumper
jumper
jumper
tshirt
shoes
cardigan
So i would the vlookup to add automatically back to list 1 tshirt, jumper and shoes.
List 1 would now look like this
accessories
belt
boots
cardigan
coat
glasses
gloves
hat
tshirt
jumper
shoes
View 3 Replies
View Related
Apr 30, 2014
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
View 1 Replies
View Related
Nov 9, 2012
I have data in three columns A, B and C. In column A I have values like:
A
1. AOL
2. BA
3. HDP
and now if cell A1 has value AOL then in cell B1 only possible values are:
DD or DP or MP or MR or RE or TP or TT
if cell B1 has value DP then in cell C1 only possible values are:
KO or MA or RS or SA or SE or UM
if cell B1 has value MR then in cell C1 only possible values are:
KO or MA or RS or SA
View 6 Replies
View Related
Mar 19, 2014
I have 2 tables on different sheets:
Table 1:
A1 B1
Product id | Contract
12 | aaa
13 | bbb
14 | ccc
15 | ddd
21 | eee
22 | fff
... |
Table 2:
A1 | B1
Product id | Contract
|
21 |
15 |
22 |
13 |
... |
I want to put the contract value on the product id correspondent on the table 2. How can i do this?
View 13 Replies
View Related
Dec 7, 2009
Excel seems to be miscalculating some formulas and I was hoping someone might have seen this before:
I'm attempting to do math on two cells and return the result in a third cell
Jx = time typed manually
AAx = time that is the result of a lookup function from data in a different worksheet
ABx = the result
Here is the formula that I am putting in ABx: =IF(ISERROR(AA39-J39),"",IF(AA39>=J39,(HOUR(AA39-J39)*60+MINUTE(AA39-J39)),-(HOUR(J39-AA39)*60+MINUTE(J39-AA39))))
J39 contains the time 18:31
and AA39 contains the time 18:30.
When the formula evaluates, the error #NUM is returned. Here are the calculation steps:
IF(FALSE,#N/A,IF(TRUE,HOUR(-.00069444444431)*60........)))
What is happening is excel is evaluating the If(AA39>=J39) as true, even when J39 contains 18:31 and AA39 contains 18:30.
View 6 Replies
View Related
Aug 3, 2009
Using VBA, I want to import data from a text file into a worksheet, but only import a row of text if the three-letter code matches one I have in a named range. The data looks similar to this:
ALF,D,06/01/2009,0.96,0.97,0.96,0.97,1007,0
ALK,D,06/01/2009,0.34,0.36,0.34,0.345,4375,0
ALL,D,06/01/2009,3.7,3.8,3.68,3.75,12381,0
ALR,D,06/01/2009,1.155,1.18,1.155,1.17,388,0
ALS,D,06/01/2009,3.39,3.39,3.23,3.31,2642,0
ALT,D,06/01/2009,0.031,0.031,0.031,0.031,3460,0
ALY,D,06/01/2009,0.1,0.105,0.1,0.105,460,0
etc...
and the named range similar to this:
AAC
AAF
AAM
AAR
AAX
ABB
ABC
ABP
ABQ
etc....
I know how to open and import the data, it's just making the comparisons that I'm having trouble with at the moment .
View 4 Replies
View Related
Nov 21, 2008
A formula that would compare any value in column 1 to any value in column 2 to see if they are a match for each other. If a match is detected it would be indicated in column 3:
A1B1C1
1221445343
3342111454
4534312214
1145433421
1144511456
View 7 Replies
View Related
Mar 14, 2013
I have a spreadsheet and everyday it would change in length.
But I would like a macro that compares the value of each cell in column B against the value in corresponding cell in column D then moves to the next roll - so eg B2 against D2,B3 against D3......B50 against D50 until all rows are compared.
And when it finds a different makes the background colours go red !
I have a bit of a code but I think I need to loop to compare all populated cells !
View 4 Replies
View Related
Jun 16, 2014
If our work for the intersection of two columns A, B supposed to be output 1, 2 and 3 Required to find the remainder of the A and show it automatically in column C The remainder of the B reveled in column D
Note: columns A,B repeat accepting of figures
To explain the result of the remaining column A is 1, 1, 5
LIST1
LIST2
RESULT
1
1
1
1
2
1
[code]....
View 9 Replies
View Related
Sep 28, 2007
If the record exists in both sheets then compare the value in col. H for both records, if the value is different then copy the entire record to a third sheet.
There are 16,000 rows of data to do comparisons.
I have been thinking about a Vlookup method as one choice, my thought is this going to be slow.
I have read about vba having some comparison choices, vbbinary, vbtext, but I do not have any knowledge of this, so cannot intelligently choose what options to begin w/.
View 9 Replies
View Related
Dec 5, 2007
i have a range of values and want to compare the value of a single cell to all those in the range, returning TRUE if a the value matches any value in the range.
View 5 Replies
View Related
Mar 3, 2008
I have a worksheet where I want to compare values in two of the columns.
In column B there are EAN codes consisting of 13 digits.
In column E there are 5-digit numbers.
What I want to do is to compare the 5 digit number with the EAN code where these digits should be in position 7-12 in the EAN code. Those numbers in column E that doesn't have a match in column B should be marked (for example that the background color of that cell is set to "red").
Example:
B E
XXXXXX12345X 12345 (match - do nothing)
XXXXXX12346X 12346 (match - do nothing)
XXXXXX12348X 12347 (no match - set background color to red)
XXXXXX12349X 12348 (match - do nothing)
XXXXXX12350X 12349 (match - do nothing)
XXXXXX12351X 12352 (no match - set background color to red)
There are more values in column E than B.
In all there are close to 4500 rows so I really could use a nifty macro to do this job.
View 9 Replies
View Related
Nov 28, 2006
I am in the process of comparing values between two sheets.
I would like to search on column A on both sheets and check whether there is a corresponding value in the sheets.
If there isnt then I would like to take that record and put it into a new sheet.
My code is as follows
Public rowcn
Sub auto_open()
Dim iCounter As Integer 'counter as integer
data_sheet = "CASES_ANALYS"
data_sheet2 = "A_CASES_"
target_sheet = "Final Report"
Currently I'm just displaying it in a msgbox but would like to paste the specific record into another sheet but not sure how to do this.
So far this code doesnt work
View 9 Replies
View Related
Dec 5, 2006
I have two columns with numbers. For every row I want to find out if either of the numbers in the two columns is in a third column that I have with numbers. Does anyone know hoe to write a formula for this?
View 9 Replies
View Related
Jul 25, 2007
I am trying to replicate a method , it reads: 'any data point that changes by more than or less than 10% from the previous value, and does not remain at the new level for more than 6 days, should be removed.'
Thats a bit vague. The process is going to involve if statements, etc. I've attached an excel spreadsheet with some solutions I've tried. Neither of which work. The aim is to get the same answers as the highlighted column. Columns B and C replicate the method answer, but do not state the process. The letters L represent values less than 25. Its the letters D I'm trying to eradicate.
View 2 Replies
View Related
Nov 26, 2007
how to compare the value in one cell on sheet "Block #1" to a list of values on sheet "Data". Specifically I want to be able to look at the value in a cell (which will be a date) and then compare it to a list of company holidays on another sheet in the workbook and if the date is a company holiday it will display the word "Holiday" in another cell I specify on "Block #1" I am doing this to automate generation of a calendar which will have 13 worksheets one for each of 13 4-week blocks. I can get the calendars generated, but can't seem to be able to get it to compare the values of each date to the list of holidays.
View 3 Replies
View Related
