LOOKUP question

D

DJ

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.
 
B

Barb Reinhardt

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.
 
R

RagDyer

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!
==============================================
 
D

DJ

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
P

Peo Sjoblom

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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

DJ

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

Peo Sjoblom said:
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 said:
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 said:
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.

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.
Click to expand...
Click to expand...
Click to expand...
 
Top