Duplicate lookup return values

JudyS · May 15th, 2002, 01:14 PM

I’m using the lookup function. The formula is =LOOKUP($A11,Sheet1!$B$1:$B$200,Sheet1!$D$1:$D$200). $A11 is for an account number in a table that is a general ledger account listing with the months going across. I want the lookup to return a value for a particular account from a specific month. The problem is that if it doesn’t find the account number in the listing, it returns “…the largest value that is equal to or less than the lookup value…” per MS Help. Otherwords, it is duplicating numbers. In the formula above it, it finds the acct # 40020, but when it looks up 40023 and doesn’t find it, it returns the value for 40020. How do I get it to return “#N/A” if it can’t find the exact account number in the list???

Juan Pablo González · May 15th, 2002, 01:16 PM

Quote:

On 2002-05-15 12:14, JudyS wrote:
I’m using the lookup function. The formula is =LOOKUP($A11,Sheet1!$B$1:$B$200,Sheet1!$D$1:$D$200). $A11 is for an account number in a table that is a general ledger account listing with the months going across. I want the lookup to return a value for a particular account from a specific month. The problem is that if it doesn’t find the account number in the listing, it returns “…the largest value that is equal to or less than the lookup value…” per MS Help. Otherwords, it is duplicating numbers. In the formula above it, it finds the acct # 40020, but when it looks up 40023 and doesn’t find it, it returns the value for 40020. How do I get it to return “#N/A” if it can’t find the exact account number in the list???

Try this formula

=VLOOKUP($A11,Sheet1!$B$1:$D$200,3,0)

JudyS · May 15th, 2002, 02:47 PM

THANK YOU!!!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 01:14 PM	#1
JudyS New Member Join Date: May 2002 Posts: 3	I’m using the lookup function. The formula is =LOOKUP($A11,Sheet1!$B$1:$B$200,Sheet1!$D$1:$D$200). $A11 is for an account number in a table that is a general ledger account listing with the months going across. I want the lookup to return a value for a particular account from a specific month. The problem is that if it doesn’t find the account number in the listing, it returns “…the largest value that is equal to or less than the lookup value…” per MS Help. Otherwords, it is duplicating numbers. In the formula above it, it finds the acct # 40020, but when it looks up 40023 and doesn’t find it, it returns the value for 40020. How do I get it to return “#N/A” if it can’t find the exact account number in the list???

May 15th, 2002, 02:47 PM	#3
JudyS New Member Join Date: May 2002 Posts: 3	THANK YOU!!!!