Is it possible to extend the number of rows a spreadsheet has? I have a table that needs 115000 rows. I copy and pasted 65505 rows and that seemed to be the end. Does anyone have a clue how to do that?

Thanks.

Not possible. About the only option is to paste into two or more spreadsheets. But the 65536 limit will not change.

is it possible to write a vlookup to search two different worksheets?

Yes.

You can use nested IF statements to test for certain criteria or perform equations on them. i.e. =IF(A1=X,VLOOKUP1(),VLOOKUP2()) & =VLOOKUP1()*VLOOKUP2()/VLOOKUP3().

Hope that helps,

Smitty

EDIT: As for your first question, if you've got that much data you might consider using Access.

Well here is my problem know. I have a worksheet with 6 columns. I need to get data out of the A column and the D column. I have written two different vlookup functions for that. Read as follows

=VLOOKUP(W129,Color!D:F,3,FALSE)
=VLOOKUP(W12,Color!A:C,3,FALSE)

Some of the values are in Column A and some are in Column D. So is there a way to write a function that merges these? It would save me a lot of work if I could drag the formula through all of my numbers instead of having to go through each one.

Thanks

Are you pulling numbers or text?

If you're trying to concatenate the information you can use:

=VLOOKUP(W129,Color!D:F,3,FALSE)&" "&VLOOKUP(W12,Color!A:C,3,FALSE) - Will return both matches with a space in between

or

=VLOOKUP(W129,Color!D:F,3,FALSE)&VLOOKUP(W12,Color !A:C,3,FALSE) - No space

Note that there's no error handling in there, so if you have blank information in either table, you'll need to add some. You can use IF(ISNA or use a range qualifier. i.e. =IF(SearchRange="","",VLOOKUP1())

HTH,

Smitty

I tried both of them and both returned the dreaded #N/A?

So here is what I typed in for the 4515 row in my sheet

=VLOOKUP(W4315,Color!D:F,3,FALSE)&VLOOKUP(W4315,Co lor!A:C,3,FALSE)

Any clue?

Now I get the #Value error.

my kingdom for an answer!

Post an example of your sheets and I/we'll take a look.

Smitty

P.S. In the future it helps to keep relevant topics in the same post - that way everyone can follow along with your what's going on.

Here is an example of what I am trying to do.

Try this:

=IF(ISNA(VLOOKUP(A1,Color!A:C,3,FALSE)),VLOOKUP(A1 ,Color!D:F,3,FALSE),IF(ISNA(VLOOKUP(A1,Color!D:F,3 ,FALSE)),VLOOKUP(A1,Color!A:C,3,FALSE)))

Or:

=IF(ISNA(VLOOKUP(A1,Color!A:C,COLUMN()-1,FALSE)),VLOOKUP(A1,Color!D:F,COLUMN()-1,FALSE),IF(ISNA(VLOOKUP(A1,Color!D:F,COLUMN()-1,FALSE)),VLOOKUP(A1,Color!A:C,COLUMN()-1,FALSE)))

Both worked just fine for me based on your example.

Smitty

Thanks a bunch!!!!