# Lookup For A Cell Value In Entire Sheet And Return Value From A Column

Apr 22, 2014

I have a sheet with Customer name in the first column and next 7 columns have unique serial numbers(alphanumeric) of the systems which that customer has purchased. In another sheet, i have the unique serial number in first column and I want to search this number in the 7 columns of other sheet and return the "Customer name" to this sheet. How do I do that ?

Vlookup searches only in 1 columns, so does match index. lookup didn't work as well.

## Lookup Text And Return Entire Row?

Oct 12, 2012

how do I search the whole text of one cell in a particular column to another cell in a different column? I then want to return the full contents of the cell that contains the text I want to find. Ie. if I want to search the text "fidelity", cell A1 in column B which contains a cell with text "fidelity asset management". I want to return fielity asset management.

## Lookup Column Values On Another Sheet & Return Adjacent Cells

Jun 11, 2008

Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b.

We have two worksheets, sheet a and sheet b

compare sheet b, column c (email addresses) to sheet a, column x, (email addresses).
If they match, move sheet a, columns d, e, f, g, and h to sheet b.

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

## Return Cell Reference From Multi-Column Lookup

Dec 13, 2006

i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)

it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!

i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).

any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!

should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?

## Find First Non Blank Cell And Return Number Above It Based On Lookup From Other Sheet

Oct 18, 2013

Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.

Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.

I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.

## Excel 2010 :: Match 1 Cell And Column In Sheet 1 To 2 Columns In Sheet 2 Return Data From A 3rd

Jul 23, 2012

I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.

My attempt is in cell D2 on sheet1.

## Excel 2010 :: Return Oldest Date In Entire Sheet

Mar 19, 2014

in Excel 2010, whether using VBA or otherwise;

1) how can I return the oldest date in the entire workbook (or at least in the entire sheet if it's not possible to find that date in the entire workbook).

2) how about the most recent date ?

## Lookup In Another Sheet And Get Top Cell Data Of Matching Column

May 9, 2014

Code:
Private Sub GetColData()
Dim SrchRng As Range
Dim SrchVal As Range

[Code]...

And it is also possible by using Column Number to retrieve the data SrchTxt, which is in a cell in the Row, in which "Roy" is found. This Row in this example is Row 5. Column Number may be anything within the search Range. Column Number is 3 in this example.

In Sheet1 "Poet" is in C1. (SrchTxt)

How to retrieve data of the cell in Row 1 in the column where "Roy" is found?

## Lookup Single Value In One Sheet, Return Multiple Results From The Other Sheet

Apr 6, 2008

i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)

what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255

the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)

is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset

after some MASSIVE googling, i have stumbled accross this

B1 = Search box (txt field)

A6 (which will be a hidden column) contains =MATCH(\$B\$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number

A7 contains =MATCH(\$B\$1,OFFSET(Data!\$A\$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number

and througout my other columns, i have
B6=OFFSET(Data!\$A\$1,A6,1)
B7=OFFSET(Data!\$A\$1,A6,2)
B8=OFFSET(Data!\$A\$1,A6,3)
and so on

2 things i cannot recitify..

1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.

## Formula To Look In A Sheet For A Cell Then Return That Value From A Different Sheets Column

Mar 4, 2009

i am using this formula to look in a sheet for a cell then return that value from a different sheets column

## Lookup/return Value Of Last Row In Sheet

Apr 23, 2007

Is there a way to get the data in the last row of a sheet and show it in another workbook? And also maybe the 2nd last row?

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

## Lookup Another Sheet & Return Correspoding Value

Aug 23, 2007

I am trying to copy onto sheet1 Colum FH date from sheet2 Colum E by matching Colum I on sheet1 with Colum B on sheet2. I would like to know if its possible.

## Lookup Value Then Return Value In Row 1 Of That Column

Nov 22, 2007

I have a list of values in column A (part numbers)

In columns B to E I have lists of similar values (part numbers, with row 1 representing their location e.g warehouse, office, etc).

In column F2 I would like to search for the value in A2 in the range B2:E100 and return the value in B1:E1 if the value appears... if it doesnt appear in any of the columns I would like 'not located' to appear in F2.

I've been trying to do this for a while but am making no progress at all.

## Lookup Value & Return Same Row Different Column

Dec 21, 2006

I am looking for a formula, to change from this
IF Formula located in Cell F15
=IF(E15=A15,B24,"")

to give the If a Range.
I thought it would simply be:

=IF(E15=A15:A39,B15:B39,"")

But all I get is Blank Cell.
B24 has a Value but it does not appear in F15,F16 and F17 respectively.

## Move Entire Row To Different Sheet Based On What In Column O

May 30, 2012

Basically Column O has data that need to be moved to the different worksheet in one workbook.

so If Column O=Bad Number in any row in Column O move to sheet1 or If Column O=Good Number in any row in Column O move to sheet 2 etc.

## Return Values Using Lookup Value From One Sheet Across Multiple Columns

Dec 11, 2012

I'm trying to find a way to:

Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"

Things to note:

a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.

b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".

If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".

If that value does not occur in sheet B, the corresponding cells should remain blank.

If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.

## Lookup & Return Data From Different Column

Apr 8, 2009

I have a book with multiple tabs in it. The first is a master customer file, including customer numbers. The next two tabs include multiple references to the same customers because they list multiple shipping addresses and contacts for each customer on a separate line. I want to be able to have the additional tabs refer to the master list and find the customer name, then return the corresponding customer # in the previous column for that customer. This is what I came up with: =IF(B:B=Sheet2!B:B,Sheet1!A:A,0). But the results simply returns numbers that increase by 1. It should repeat the same customer # for the number of times that customer name is listed based on how many addresses or contacts there are. What am I not doing right?

## Lookup Value - Return Column Heading

Apr 14, 2010

I want to lookup the max value within a group of columns and return the column heading (specified a cell) that corresponds to the max value.

## Moving Entire Row To Another Sheet When Column Shows Closed Value

Feb 22, 2014

As I am at work, it does not allow me to upload the file due to restrictions on uploading. However, the spreadsheet is fairly simple so should be able to describe it.

The main sheet is called "Investigation Court Apps". An entry is made into each row and the status shows as "Open" in Column A, until a final closure date is input in Column Z, at which point the entry in Column A changes to "Closed". This is achieved with the following IF statement in Column A.

=IF(B3="","",IF(ISNUMBER(\$Z3),"Closed","Open"))

So that part is all automated. What I need is some VBA coding or macro (might be same thing but completely new to all this) that when the value in Column A changes to "Closed" the whole row moves over to "Sheet3". Once the row has been moved over, I want to ensure there are no blank rows also.

I have tried the following amongst others:

Sub MoveToSheet3()
Dim C1 As Range
Dim RowNum As Integer
For Each C1 In Sheets("Investigations Court Apps").Range("N1:N" & Range("A65536").End(xlUp).Row)
If Cll = 0 Then

[Code]....

I know to open VBA its Alt+F11 and to close Alt+Q, but thats about it.

## How To Copy Entire Row To New Sheet Based On Values In Column A

Nov 24, 2011

I am trying to copy the entire row based on values in column A. I have 13237 rows of data. Column A is grouped into about 200 categories with corresponding data in B-F. I would like this to automatically copy the data into new tabs based on the groups in column A. I would also like the tab to be renamed to the value in column A.

M195_-_Subinventory_Item_Locato  ABCDEF1LocatorItemSERIAL_NUMBERLOT_NUMBERON_HANDUOM201
.REEF..22700300100 228136EA301.REEF..22643400000 331122EA401
.REEF..K20-745-000 531124EA501.REEF..K20-618-000 531132EA601
.REEF..22747300000 1122111EA701

[Code] .........

## Copy Entire Row To New Sheet If Column Data Is Matched

Jun 12, 2009

i need a macro that will allow an entire row from one sheet be copied into a new sheet.

so basically here is what needs to be done:

1. from my active sheet, look in Column 'D', select all rows in in column D if it has the word "Confirmed" in it

2. create a new sheet and paste all those selected rows to this sheet

3. delete this transfered data from sheet 1

4. in the new sheet, i need to add in titles as follows in columns A - O:

Ticket
OrgCode
Event
Event Status
Project Type
Project Status
Start Date
End Date,.....................

## Lookup Of Row (return Data From Specified Row) With Column Heading And Value

Feb 21, 2014

Encryption algorythim.xlsx

Here is my scenario I have all this data i want to search in. In the rows there are repeating values and in the columns they do not repeat.

As seen in the attached i am able to give a column and row by title and then get the intersecting cell data from that. (Encryption lookup)

What i want to do is give the column tittle and row value and get back the row title. (Decryption Lookup)

This shouldn't be too hard seeing that the data in the columns has no repeats.

## Conditional Lookup (two Criteria) And Return Value Of Different Column

Apr 13, 2012

I recently learned how to count cells in a range based on the value from another column (excel 2007) How to count cells in a range based on the value from another column but now how I can go about returning a value from another column that matches the conditional counting. For example in the table below I'm first wanting to find the rows matching "chr15" from column A that also have a value from column B that is greater than 25,000 and less than 3,000,000. But what it I wanted to instead report the corresponding values in column C? I've played with VLOOKUP to no avail but I'm not sure if that's the right line of thinking. The answer would be the values in bold.

A B C
1 chr2 12008 AA
2 chr2 149700 BB
3 chr15 51 CC
4 chr15 5624 DD

[Code] .........

Nov 18, 2008

I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.

Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.

## Lookup Data Without Knowing Return Column

Aug 25, 2006

Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)!

I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet.

The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells.

I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.

## Lookup Left Most Column & Return Adjacent Value

Oct 1, 2007

Basically what I am trying to do is use a Lookup formula on a sales invoice where the product name and product price will be automatically entered into the sales form when the product ID is entered. What would the formula be?

Product Information
RegionProduct IDProduct NameProduct Price
Product IDHPPHomeware Pizza Pan19.95
Product NameSPPStone Pizza Pan 29.95
Product PriceHCDHomeware Casserole Dish19.95
Total SaleCWChina Wok 19.95
HEWHomeware Electric Wok29.95
WCMWilson Coffee Machine29.95
HRHomeware Rotisserie119.95
OGGOlson Gas Grill 159.95
OEGOlson Electric Grill 159.95

## Copy Entire Row To New Sheet Based On Value Of Column Within Multiple Sheets?

Mar 27, 2014

I am trying to find a way to copy the contents of an entire row to a new sheet in a workbook based on the value of a column, specifically column K with the value of "good". There are 3 sheets that im searching. This is where I run into problems. Im looking to start with sheet1, and copy all rows deemed "good" into entries on sheet 4. Then search sheet2 and copy all relevant entries into the next available row on sheet4, and then repeat on sheet3.

## Lookup Up Entire Table Not Just Column In Table Like Vlookup Does

Jun 5, 2014

I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?

I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.

i.e.
A B C D

territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555

and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.