Short answers:
Yes - you can use VLOOKUP() within nested (IF) statements.
No - VLOOKUP() only returns a single value out of the matched row
No - VLOOKUP() can't return data to the left of the column the match is
sought in, But!! the LOOKUP() function can do that.

All of the various lookups (HLOOKUP, VLOOKUP and LOOKUP) are pretty much
limited to finding the first entry meeting the lookup parameters, although
LOOKUP can be used to "You can also use the LOOKUP function as an alternative
the IF function for elaborate tests or tests for more than seven conditions.
See the examples in the array form." - from the Help topic on LOOKUP

But in your case, I think that a macro is probably going to be the better
way to develop a solution since you want to return entire rows and you need
to look for mulitple occurances of the same value in your data.

Not knowing how familiar you are with writing macros or coding in general,
it's difficult to point you to far. But I'll give a rough logic flow for you:

The could would have to go to the sheet with the list of charge codes to
find on the other sheets and then loop through all cells containing those
charge codes, on at a time, saving the contents for comparisons on the other
sheets. Then for each sheet with data to be matched it would go to the
beginning of the list and look through it for matches and on each match, copy
that row to a sheet designated to be used to receive those rows of
information, then move to next sheet and repeat and once it has examined all
sheets, then move to the next cell on your charge code sheet, get the next
lookup value and work through the other sheets from beginning to end again.
This could be quite time consuming if you have really long lists.

Do you want to give the macro solution a shot?


"Aine" wrote:

Hi All,

Sorry, this may seem like an elementary question but....

First of all I have three seperate worksheets containing data within my
workbook.

I want to return all rows from each of those worksheets that matches a
certain criteria to another worksheet, without having to manipulate the
data.

e.g.
I have charge codes beginning in 1-90XX, 1-91xx, etc...
These appear multiple times in the worksheets that contain data
I want to search sheets 1 - 3 to see if any rows contain them & if they
do, I want all these complete rows to appear in another worksheet
within that same workbook.



VLOOKUP function will not work for me as the charge code is in column O
& I can only get it to return data within that row for column O onward.

Also, I am unsure if you can get VLOOKUP to check for multiple
conditions

* What function should I be using?
* Can you use VLOOKUP in a nested statement to search for the various
conditions(charge codes)?
* Can VLOOKUP return a whole row, without having to specify the column?
* Can VLOOKUP return data previous to the column where the criteria of
the search is met?

* Finally: Should I try to use Macros & if so, can someone point me in
the right direction???


Thanks,

Aine

Should have been...
"The code would have to...", not "The could would have to..."

"JLatham" wrote:

Short answers:
Yes - you can use VLOOKUP() within nested (IF) statements.
No - VLOOKUP() only returns a single value out of the matched row
No - VLOOKUP() can't return data to the left of the column the match is
sought in, But!! the LOOKUP() function can do that.

All of the various lookups (HLOOKUP, VLOOKUP and LOOKUP) are pretty much
limited to finding the first entry meeting the lookup parameters, although
LOOKUP can be used to "You can also use the LOOKUP function as an alternative
the IF function for elaborate tests or tests for more than seven conditions.
See the examples in the array form." - from the Help topic on LOOKUP

But in your case, I think that a macro is probably going to be the better
way to develop a solution since you want to return entire rows and you need
to look for mulitple occurances of the same value in your data.

Not knowing how familiar you are with writing macros or coding in general,
it's difficult to point you to far. But I'll give a rough logic flow for you:

The could would have to go to the sheet with the list of charge codes to
find on the other sheets and then loop through all cells containing those
charge codes, on at a time, saving the contents for comparisons on the other
sheets. Then for each sheet with data to be matched it would go to the
beginning of the list and look through it for matches and on each match, copy
that row to a sheet designated to be used to receive those rows of
information, then move to next sheet and repeat and once it has examined all
sheets, then move to the next cell on your charge code sheet, get the next
lookup value and work through the other sheets from beginning to end again.
This could be quite time consuming if you have really long lists.

Do you want to give the macro solution a shot?


"Aine" wrote:

Hi All,

Sorry, this may seem like an elementary question but....

First of all I have three seperate worksheets containing data within my
workbook.

I want to return all rows from each of those worksheets that matches a
certain criteria to another worksheet, without having to manipulate the
data.

e.g.
I have charge codes beginning in 1-90XX, 1-91xx, etc...
These appear multiple times in the worksheets that contain data
I want to search sheets 1 - 3 to see if any rows contain them & if they
do, I want all these complete rows to appear in another worksheet
within that same workbook.



VLOOKUP function will not work for me as the charge code is in column O
& I can only get it to return data within that row for column O onward.

Also, I am unsure if you can get VLOOKUP to check for multiple
conditions

* What function should I be using?
* Can you use VLOOKUP in a nested statement to search for the various
conditions(charge codes)?
* Can VLOOKUP return a whole row, without having to specify the column?
* Can VLOOKUP return data previous to the column where the criteria of
the search is met?

* Finally: Should I try to use Macros & if so, can someone point me in
the right direction???


Thanks,

Aine

That would be ideal.

Have a good background in programming & logic, but have never touched
macros
I normally work with databases but the company I am contracting for
want it all through excel for visibility purposes(plus they prefer us
not to use MS Access)

Do you know any good site with macros tutorials for these types of
queries?

To be honest, I don't roam around a lot looking for code solutions - I
probably should do more of that since they almost always teach something new.

But an excellent book on practical programming within Excel was written by
John Walkenbach - I have a copy titled "Excel 2000 Power Programming with
VBA" (ISBN 0-7645-3263-4, and I believe there's an updated edition "Excel
2003 ..." Should be available through Amazon or your local Barnes & Noble,
Borders, etc.

"Aine" wrote:

That would be ideal.

Have a good background in programming & logic, but have never touched
macros
I normally work with databases but the company I am contracting for
want it all through excel for visibility purposes(plus they prefer us
not to use MS Access)

Do you know any good site with macros tutorials for these types of
queries?

If you want an example of code that will work for this, you can upload a
workbook I quickly tossed together from:
http://www.jlathamsite.com/uploads/codebook_foraine.xls

If you just want to see the working code (which is dependent on layout and
sheet names used in the sample workbook), here it is. It presumes that no
list goes all the way to the end of a worksheet - doesn't trap for that
situation.

Sub GatherDataTogether()
Dim ToBeCompared As String

Dim ListSheet As String
Dim CollateSheet As String
Dim DataSheets(1 To 3) As String
Dim SecondDataSheet As String
Dim ThirdDataSheet As String
Dim ListRowOffset As Long
Dim DataOffsetRow As Long
Dim LC As Integer ' loop counter

'initialize to find our sheets later
ListSheet = "ListSheet"
CollateSheet = "GatheredSheet"
DataSheets(1) = "Sheet1"
DataSheets(2) = "Sheet2"
DataSheets(3) = "Sheet3"

'get ready
'activate the next instruction to speed things up
'Application.ScreenUpdating = False
Worksheets(CollateSheet).Activate
'choose place to move 1st match to
Range("A1").Select
Sheets(ListSheet).Activate
'choose first 'to match' entry
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ToBeCompared = ActiveCell.Value
For LC = LBound(DataSheets) To UBound(DataSheets)
Worksheets(DataSheets(LC)).Activate
Range("A1").Select ' start of data
DataOffsetRow = 0 ' (re)initialize
Do Until IsEmpty(ActiveCell.Offset(DataOffsetRow, 0))
If ActiveCell.Offset(DataOffsetRow, 0) = ToBeCompared Then
DataOffsetRow = DataOffsetRow + 1
Rows(DataOffsetRow & ":" & DataOffsetRow).Copy
'move to destination sheet
Sheets(CollateSheet).Activate
'Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Activate ' prepare for next
'back to data sheet for more checking
Sheets(DataSheets(LC)).Activate
Range("A1").Select ' back to top of list
Else
DataOffsetRow = DataOffsetRow + 1
End If
Loop ' data on current data sheet
Next ' LC loop
Sheets(ListSheet).Activate
ActiveCell.Offset(1, 0).Activate ' move down 1 row
Loop ' lookup list
End Sub


"Aine" wrote:

That would be ideal.

Have a good background in programming & logic, but have never touched
macros
I normally work with databases but the company I am contracting for
want it all through excel for visibility purposes(plus they prefer us
not to use MS Access)

Do you know any good site with macros tutorials for these types of
queries?

Aine,

If you were to use MSQuery you can return all the rows from each sheet or
you can try a pivot table that could consolidate and totalise!

"Aine" wrote in message
oups.com...
That would be ideal.

Have a good background in programming & logic, but have never touched
macros
I normally work with databases but the company I am contracting for
want it all through excel for visibility purposes(plus they prefer us
not to use MS Access)

Do you know any good site with macros tutorials for these types of
queries?