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