VLOOKUP - I THINK I NEED SOMETHING MORE

HAWK · Apr 5th, 2002, 09:44 PM

CURRENT FORMULA:
=IF(ISNA(LOOKUP(B19,$N$16:$S$34)),"",LOOKUP(B19,$N$16:$S$34))

I NEED TO EXPAND ARRAY TO 60 ROWS. IT WOULD
BETTER IF I DID NOT HAVE TO SORT FIRST ROW IN NUMBERICAL ORDER. THIS IS FOR A SHIPPING DOCUMENT - WE ENTER INVENTORY ITEM # INTO B19
I WOULD LIKE TO SORT ARRAY BY PRODUCT DESCRIPTION (COLUMN S)

I AM LOOKING FOR AN ALTERNATE FORMULA

I APPRECIATE ANY HELP. THANK YOU IN ADVANCE
FOR YOUR TIME.

Anne Troy · Apr 5th, 2002, 10:02 PM

See MyVlookup at:

http://www.thewordexpert.com/tipwarez.htm

It explains the true/false argument that does not require you to sort...

Ricky Morris · Apr 5th, 2002, 10:03 PM

Try using the false parameter in the vlookup function to avoid sorting your lookup table.

ex: vlookup(value,table,3,false)

Anne Troy · Apr 5th, 2002, 10:50 PM

PS: Hawk. Also check out how I use named ranges for my lookup areas. No need to change data range when you use named ranges for entire rows or columns.

Jack in the UK · Apr 7th, 2002, 03:01 AM

Hi Dreamboat

Can i suggest posting the answer rather that just pasting a shortcut hyper link to you home page, guys are asking questions and so would be nice if that answer was on this site for us all to see and use.

And so others can search....

Dave Hawley · Apr 7th, 2002, 03:42 AM

Hi Hawk

In addition to the inforamtion already supplied (in particular the False argument for VLOOKUP). Consider using a COUNTIF to test whether the content of B19 is within your data table.

=IF(COUNTIF($N$16:$N$34,B19)=0,"",LOOKUP(B19,$N$16:$S$34,FALSE))

Apr 5th, 2002, 10:02 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	See MyVlookup at: http://www.thewordexpert.com/tipwarez.htm It explains the true/false argument that does not require you to sort... __________________ ~Anne Troy

Apr 5th, 2002, 10:03 PM	#3
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Try using the false parameter in the vlookup function to avoid sorting your lookup table. ex: vlookup(value,table,3,false) __________________ It's never too late to learn something new. Ricky

Apr 5th, 2002, 10:50 PM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	PS: Hawk. Also check out how I use named ranges for my lookup areas. No need to change data range when you use named ranges for entire rows or columns. __________________ ~Anne Troy

Apr 7th, 2002, 03:01 AM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Hi Dreamboat Can i suggest posting the answer rather that just pasting a shortcut hyper link to you home page, guys are asking questions and so would be nice if that answer was on this site for us all to see and use. And so others can search.... __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

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

Apr 5th, 2002, 09:44 PM	#1
HAWK New Member Join Date: Mar 2002 Location: LIZ Posts: 19	CURRENT FORMULA: =IF(ISNA(LOOKUP(B19,$N$16:$S$34)),"",LOOKUP(B19,$N$16:$S$34)) I NEED TO EXPAND ARRAY TO 60 ROWS. IT WOULD BETTER IF I DID NOT HAVE TO SORT FIRST ROW IN NUMBERICAL ORDER. THIS IS FOR A SHIPPING DOCUMENT - WE ENTER INVENTORY ITEM # INTO B19 I WOULD LIKE TO SORT ARRAY BY PRODUCT DESCRIPTION (COLUMN S) I AM LOOKING FOR AN ALTERNATE FORMULA I APPRECIATE ANY HELP. THANK YOU IN ADVANCE FOR YOUR TIME.

Apr 7th, 2002, 03:42 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Hawk In addition to the inforamtion already supplied (in particular the False argument for VLOOKUP). Consider using a COUNTIF to test whether the content of B19 is within your data table. =IF(COUNTIF($N$16:$N$34,B19)=0,"",LOOKUP(B19,$N$16:$S$34,FALSE))