By your use of relative and absolute references in your formula, I'm
assuming that you are copying your formula down a number of rows as well as
across numerous columns.
Since your lookup range is rather large (50,000 rows), the following
approach might save you some efficiency.

Let's perform a single search and use the results of that search as the
criteria for returning the entire row of data from the array.

Start with something like this in say H5 of Sheet2:

=IF(ISNA(MATCH(G5,Sheet1!A$1:A$50000,0)),"",MATCH( G5,Sheet1!A$1:A$50000,0))

If there is a match in the datalist A1 to J50000, this will return the row
number ... OR ... a blank cell if no match was found.

Copy this formula down as far as needed.

Now, in the formulas in the next columns over, we use the results of this
single search to return either data or empty cells.

In cell I5 enter:
=IF($H5="","",INDEX(Sheet1!$A$1:$J$50000,$H5,COLUM NS($A:B)))

Copy this formula across to Q5,
Then, select I5 to Q5, and copy that 9 cell selection down as far as needed.

This approach should improve the speed of your query.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Dylan @ UAFC" wrote in message
...
Thank short cut on the filing the arrays out is great.
Do you have any suggestion for speed that would run better that
if(isna('vlookup forumula","vlookup formula")
When I add this to take away the #n/a's it
kills the speed

"Gord Dibben" wrote:

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell