|
LOOKUP question
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. |
| All times are GMT +1. The time now is 01:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com