My problem is that my formula is returning values for which there shouldn't
be a value. Here's the first formula...

=LOOKUP(A4,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

It returns the proper result.

The next line has this formula...

=LOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

There is no match for "A5" in the other spreadsheet, so I'd like it to
ruturn either a "0" or nothing. Instead, it returns the same results as the
previous line.

Thanks in advance for any help.

Why not use

=VLOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$B$1000,2,false)

You'll know when you don't have a match.

"DJ" wrote in message
...
My problem is that my formula is returning values for which there
shouldn't
be a value. Here's the first formula...

=LOOKUP(A4,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

It returns the proper result.

The next line has this formula...

=LOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

There is no match for "A5" in the other spreadsheet, so I'd like it to
ruturn either a "0" or nothing. Instead, it returns the same results as
the
previous line.

Thanks in advance for any help.

The Lookup() function does not posses the ability to *only* return exact
matches.
Try Vlookup() instead.

=VLOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$B$1000,2,0)

Now, when no exact match is found, you'll get an #N/A error.

The formula gets longer if you want a "0" or "" (null) return instead.

=IF(ISNA(MATCH(A5,'[Sortable Complete
Records.xls]Sheet1'!$A$4:$A$1000,0)),0,VLOOKUP(A5,'[Sortable Complete
Records.xls]Sheet1'!$A$4:$B$1000,2,0))

--
HTH,

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



"DJ" wrote in message
...
My problem is that my formula is returning values for which there
shouldn't
be a value. Here's the first formula...

=LOOKUP(A4,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

It returns the proper result.

The next line has this formula...

=LOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

There is no match for "A5" in the other spreadsheet, so I'd like it to
ruturn either a "0" or nothing. Instead, it returns the same results as
the
previous line.

Thanks in advance for any help.

Ok, that worked fine for column B. Unfortunately, it's not going to work for
the next three columns (C,D, and E). Not sure how to change the formula for
each column.

"Barb Reinhardt" wrote:

Why not use

=VLOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$B$1000,2,false)

You'll know when you don't have a match.

"DJ" wrote in message
...
My problem is that my formula is returning values for which there
shouldn't
be a value. Here's the first formula...

=LOOKUP(A4,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

It returns the proper result.

The next line has this formula...

=LOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

There is no match for "A5" in the other spreadsheet, so I'd like it to
ruturn either a "0" or nothing. Instead, it returns the same results as
the
previous line.

Thanks in advance for any help.

the 2 is the column index number so if 2 gives you column B then 3 will give
you C and so on, you can replace the index number with

COLUMN(B;B)

change A5 to $A$5 and copy the formula across

--
Regards,

Peo Sjoblom

(No private emails please)


"DJ" wrote in message
...
Ok, that worked fine for column B. Unfortunately, it's not going to work
for
the next three columns (C,D, and E). Not sure how to change the formula
for
each column.

"Barb Reinhardt" wrote:

Why not use

=VLOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$B$1000,2,false)

You'll know when you don't have a match.

"DJ" wrote in message
...
My problem is that my formula is returning values for which there
shouldn't
be a value. Here's the first formula...

=LOOKUP(A4,'[Sortable Complete
Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

It returns the proper result.

The next line has this formula...

=LOOKUP(A5,'[Sortable Complete
Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

There is no match for "A5" in the other spreadsheet, so I'd like it to
ruturn either a "0" or nothing. Instead, it returns the same results as
the
previous line.

Thanks in advance for any help.

Thanks all! Got it now. I might be dangerous if I had any idea what I was
doing with these sheets...

"Peo Sjoblom" wrote:

the 2 is the column index number so if 2 gives you column B then 3 will give
you C and so on, you can replace the index number with

COLUMN(B;B)

change A5 to $A$5 and copy the formula across

--
Regards,

Peo Sjoblom

(No private emails please)


"DJ" wrote in message
...
Ok, that worked fine for column B. Unfortunately, it's not going to work
for
the next three columns (C,D, and E). Not sure how to change the formula
for
each column.

"Barb Reinhardt" wrote:

Why not use

=VLOOKUP(A5,'[Sortable Complete Records.xls]Sheet1'!$A$4:$B$1000,2,false)

You'll know when you don't have a match.

"DJ" wrote in message
...
My problem is that my formula is returning values for which there
shouldn't
be a value. Here's the first formula...

=LOOKUP(A4,'[Sortable Complete
Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

It returns the proper result.

The next line has this formula...

=LOOKUP(A5,'[Sortable Complete
Records.xls]Sheet1'!$A$4:$A$1000,'[Sortable
Complete Records.xls]Sheet1'!$B$4:$B$1000)

There is no match for "A5" in the other spreadsheet, so I'd like it to
ruturn either a "0" or nothing. Instead, it returns the same results as
the
previous line.

Thanks in advance for any help.