Rows Value Matches Lookup Value

Nov 16, 2006

The table i'm looking up contains 2 rows with a value which matches my lookup value, how do i get the vlookup to pick up both values?

this is what i have:
ACEUSA-0017,730.84
SISTRUINSU-0018,865.69
WARNORAMER-00212,868.59
ACEUSA-00113,948.61

This is what vlookup picks up:
ACEUSA-001 7,730.84
ACEUSA-001 7,730.84

using this formula:
IF(ISNA(VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE)),0,VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE))

This is what i need it to pick up
ACEUSA-001 7,730.84
ACEUSA-001 13,948.61

I do need ISNA to take care of #N/A

View 14 Replies


ADVERTISEMENT

Lookup Up Matches From Other Sheet

Aug 26, 2009

Worksheet #1:
Column "A" going down (starting at A1 to A5) I have the numbers 1,2,3,4,5 entered in each cell...

Worksheet #2:
In cell A1 is the number "1"
In cell A2 is the number "7"

I want a formula in cell B1 (WS#2) that looks for the number in cell A1 (WS#2) in the range of cells A1:A5 on Worksheet #1, and if it finds the value of A1 (WS#2) in that range of cells on Worksheet #1, it returns the letter Y... if not it returns the letter N

So my result on Worksheet #2 should be...
Cell B1 shows the letter Y
Cell B2 shows the letter N

View 2 Replies View Related

Add A 3rd Lookup To A Formula With 2 Current Matches

Jan 26, 2010

a while back one of you gave me an awesome formula that pulls data from sheet 2 based on what was typed in A1 and A2 on sheet 1. Well I ran into a snag, is there a way we can add a 3rd match in the formula. See the attached file. I need to pull data from sheet 2 based on what is typed on sheet 1 in cell A1, A2 and A3.

Exp
Sheet 1, report to be printed
A1 = last name
A2 = first name
A3 = month

Sheet 2, data is held here
Need to pull data in column “M”

View 2 Replies View Related

Lookup Values Where There Are Duplicate Matches

Jan 27, 2012

When performing a VLOOKUP, it returns the first instance of a match in the the lookup range.

So in the spreadsheet I have, I'm performing a vlookup....and finding a match...and then returning a value 1 column to the right...

What I "need" however is a formula that will accept that there is more than 1 row where the match exists and total all the values for that match.

I vaguely understand array formulas but have not been successful and am looking for some tips ...guidance etc....

View 7 Replies View Related

Lookup Values When There Are Multiple Matches?

Mar 25, 2012

how do i lookup values when there are multiple matches and i want to return values horizontally

eg

column A Column B
A 1
B 2
C 3
A 4
B 5
C 6
D 7
D 8
D 9
D 10

and the output should be like:

column A Column B Column C Column D Column E
A 1 4
B 2 5
C 3 6
D 7 8 9 10

View 7 Replies View Related

Lookup Particular Row Information If Cell Value Matches Specified Value

Feb 10, 2013

I have two worksheets, one named Data and one named Index. In column G of Data, there is an index number. In column G of Index, there is a list of special index numbers.

In columns A:F there is information about each special index numbers.

What I need is in Data, if the index number of column G exactly matches the value of a special index number in Index Column G, put the column A:F information for that row in the row with matching index number in Data.

Data Worksheet

A
B
C
D
E
F
G

1
header row

[Code] ......

I need to be able to put the formulas in A2 to F2 and just drag the formula down all ~5000 cells of Data, and only need the information put into the ~ 20 special rows, determined by the index number in column G.

View 5 Replies View Related

Lookup Max Which Matches A Time In List

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

Lookup Formula That Matches First Three Digits Of Column

May 7, 2014

I need a lookup formula that matches first three digits of column, U (153 - LINE PIPE), and match Total 13153 - LINE PIPE, 7th, 8th, and 9th digit in column C. and bring value from either column T,

The formula that I am using is not obviously not correct, I am putting it here to show what I am trying to do.

{=INDEX($T$2:$T$16,MATCH(LEFT(S2,3),MID(C2:C273,FIND(" ",C2),3)),FALSE)}

View 5 Replies View Related

Lookup Function Required To Return Multiple Matches

Apr 24, 2007

I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...

My column headings are as follows...
Type Region Contact Phone Email ADD1 ADD2 ADD3

In column A (type) would be the type of contact eg:business/personal/family etc.

So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense!
There are over 1000 records i need excel to search through & return matches.

I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.

View 9 Replies View Related

MACRO Lookup Columns Of Current Sheet And Matches It With Table In Tab Then Returns Hourly Pay In Column

Mar 5, 2014

I am having trouble with my macro. I have attached excel sheet for reference.

Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.

I have created the macro but it's giving me Run time 1004 error.

Also, my formula is incorrect. Maybe that's why my macro isn't working?

2WayLookup_Macro.xlsm‎

View 4 Replies View Related

Multiple Lookup Values Rows And Columns To Lookup Single Target Column On Right End?

Apr 7, 2014

I have a table of data (say Column1 to Column 5) with multiple rows.

Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.

I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.

It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.

View 3 Replies View Related

Code Matches Two Rows With Six Cells In Different Sequences

Oct 22, 2013

Worksheet cells A1 – A25 have sequential numbers 1 – 24

the following elegant code generates every permutation of six numbers from the list into Cols C – H

VB:
Option Explicit
Sub Combinations()
Dim rRng As Range, p

[Code].....

View 7 Replies View Related

Getting Rows From Separate Sheet That Matches A Condition?

Mar 10, 2014

If I have these on Sheet 1

Name Age Cutoff
PersonOne 27 21
PersonTwo 21 20
PersonThree 19 19
PersonFour 17 20

generate/list/display on the next sheet (Sheet 2) of all the rows that has AGE less or equal to Cutoff, so on Sheet 2 based on the Sheet 1 data, I should get the following rows listed:

Name Age Cutoff
PersonThree 19 19
PersonFour 17 20

View 10 Replies View Related

Lookup Up/Match: Return A Value In Column A If My Data Matches Column B?

Jun 5, 2009

I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.

View 4 Replies View Related

Match 2 Columns Across Two Sheets & Copy Rows Of Matches

Jan 20, 2009

I would like to match column data in a source spreadsheet to column data in a target sheet. If a match is found, I would like to copy the corresponding row range from the source sheet to a separate, third sheet. For values where no match in found in the a target sheet, I would color the unmatched cell in the target sheet red. If a match was found, the cells would be colored green. The data in the Source sheet is in column A, while the Data in the Target sheet is in Column T. The data will be pased in the third sheet in Column T preserving original formats

I have this code, gleaned from several postings on this forum that somewhat works. The problem is that I get false mismatches (i.e. some cells get colored red even when there is a match and the data got copied to the third sheet) even though there are no duplicates. I have made sure that the formats are identical in both Target and Source sheets to try to fix this. Also, I don't want to cut the entire row , but just copy and paste a row range onto a third sheet. The column and row ranges are variable. I am attaching a file.!!

Sub CutRows()
Dim i As Long, k As Long, n As Variant, r As Range
Application. ScreenUpdating = False
With Sheets("Source")
Set r = Range(.Cells(1, 9), .Cells(65536, 6).End(xlUp))
End With
k = 0
i = 6
While Not IsEmpty(Sheets("Target").Cells(i, 20))
n = Application.Match(Sheets("Target").Cells(i, 20).Value, r, 0)
If IsNumeric(n) Then
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 35
k = k + 1
Sheets("Source").Rows(n).Cut Sheets("Sheet3").Rows(k)
Else
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 3
End If
i = i + 1
Wend
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

View 7 Replies View Related

Delete Rows If Opposite Column Cell Matches

Sep 6, 2006

I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!

Sub DeleteRepeatedRows()
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
rCell.EntireRow.Delete
End If
If rCell.Value = rCell.Offset(2, 0).Value Then
rCell.EntireRow.Delete
End If.........................................

View 7 Replies View Related

Cross Check 2 Ranges For Matches & Return Row Number Of Matches

Apr 29, 2009

I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK

View 3 Replies View Related

Return Matches & Non-Matches From Delimited Cells Against List Range

Jan 29, 2010

In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":

1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List

2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List

3.) Report In Column D: Identify and return all unique values in "Column C" as a List.

Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"

View 4 Replies View Related

Comparing Two Columns On Different Worksheets And Adding Rows Where Matches Occur

Jan 18, 2014

Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).

Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line

VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer

[Code].....

View 5 Replies View Related

Placing Numbers In Sequence Down Rows And Duplicating If Information Matches?

Mar 7, 2013

I have 2 columns,

column A has a series of 8 digit numbers (some will match some will not) sorting in A-z order from lowest to highest value.

Column B is blank at the moment.

What I want

In column B i need to add numbers starting at 1 and moving down in sequence.

If more than one rows have the same number in column A they get the same numbe rin Column B

It's really a pain or have to hand type in the numbers and I can't figure out a easy way to do this.

Attached example.

Tab 1 = Origanl Data
Tab 2 = results i'm lookin for.

View 2 Replies View Related

Color Table Rows Where Specific Cell In Row Matches Condition

Jul 2, 2008

I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.

Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1) . Interior.ColorIndex = 16

View 4 Replies View Related

Macro To Copy Data From Columns And Paste In Rows After Given Criteria Matches?

Nov 21, 2011

I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.

CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011

I get the data in the below format

CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011

I need to the macro to get the data not from the second cell.

Below is my macro

Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item

[code]....

View 2 Replies View Related

Delete Rows If Column Has Fewer Than X Matches To Part String Match

May 5, 2008

I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.

Data set example:

In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.

Dim c As Long
c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
With Range("A3:A" & c)
If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then
. AutoFilter field:=1, Criteria1:="1/*"
.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
.AutoFilter

I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.

View 6 Replies View Related

Cell Return All Matches From A List And Have The List Of Matches Reduce As You Type

Jan 28, 2009

is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?

View 3 Replies View Related

How To Count A Column If It Matches Data From Another Column In Seperate Rows

Feb 6, 2009

I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.

Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.

View 14 Replies View Related

Lookup With Multiple Rows.

Sep 22, 2009

I have been trying to auto update a table by looking up information from a table on top. However, for the top table there are multiple entries in the same column, and my look up only checks the first row and ignores the rest.

My formula at this point is: =IF(ISERROR(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))=TRUE, "", IF(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4)=0, "", INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))).

If you can take a look at my attachment, this formula is in the cells I40:W40. Eventually, I will want to populate the entire bottom table with the same formula.

View 2 Replies View Related

Lookup And Copy Rows

Jan 30, 2010

Running Excel 2007, I have attached an example of the spreadsheet I'm working on. The actual spreadsheet contains many more sheets and thousands of rows, with several more locations, but the concept is the same.

Challenge 1:
I want to search Computer Name (column C) for names starting with AKL, WEL or WLG and then copy those rows to the NZ worksheet (leaving the original worksheet rows intact).
Then for names starting with DEN, copy rows to the USA sheet.
Then similar for other names...

I have been exhausting myself by experimenting with all sorts of nested INDEX, MATCH and VLOOKUP formulas, but I'm not up to the task and suspect a vb macro is the best or only way to do this..?

I found this post but I haven't been able to adapt it to work in my sheet... probably just my inexperience... http://www.excelforum.com/excel-prog...ell-value.html

Challenge 2:
The next time the IT dept gives me an updated asset report, I will copy all the relevant data into this 'All Data' sheet, replacing the old data that was there - can the NZ and USA sheets dynamically adjust, or do I have to delete what's there to guarantee I won't accumulate old data? I guess I could use a blank 'template' file each time, if this is easier than building the function into vb...

Once the data is organised in this way, I intend to create a summary sheet that will show a count of assets in each location, probably with a stacked chart to pretty it up.

View 7 Replies View Related

Possible Multiple Rows LOOKUP

Jan 10, 2008

I have a worksheet which Sorted in ColA contains Product Id#, ColB contains Vendor, ColC contains Grade# (1 thru 6). Now there may be multiple Vendors (ColB) for same Product # (ColA) but with different Grade# ColC).

I would hope if you can please help me formulate for ColD to pull (list) ALL Vendors for the same Product# (there maybe as many as 6) PROVIDING that the Grade# (ColC) is either 3 or 4.

View 9 Replies View Related

Lookup Many Rows And Columns

Dec 29, 2002

My daily receiving list has Item# received that day e.g.
ReceiptList.xls
Item#
123
456
I need to compare this to a Data Base list that has 10000 item# , seller, and status (this data base can have repeated same item# but varied seller and therefore different status
e.g.
Ing.xls (Data Base file)
Item# Seller Status
123 ABC Approved
123 GMC Not Approved
456 ABC Pending
456 XYZ Not Approved
456 QRS Approved
etc. +10000 items
Now I need to compare the daily receipts to the data base file and pull all the sellers for those particular items and their status which like I said could be on many rows, one row, or not listed at all to the ReceiptList.xls(which I can then make sure that the received items are from an Approved source etc.)

View 13 Replies View Related

Lookup But Offset X Rows

Dec 16, 2006

I have a worksheet of data that I am trying to reference to. I will have the data as a "dump" on one worksheet, and lookup from that sheet. I've attached an example of the data dump. I need to essentially VLOOKUP the value (i.e. 054_6425) and then count over 3 columns, and down two rows. I've tried INDEX MATCH, but can't get that to work. Can you get VLOOKUP to count over and down and return a value? File "example" is attached.

View 2 Replies View Related







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