URGENTTTTTTTTT!!!! I REALLY NEED HELP - VLOOKUP

Sami · Mar 16th, 2002, 10:20 AM

I have created a VLOOKUP for searching for the product name in the product workbook when the product ID has been entered:

=VLOOKUP(ProductID,Products.xls!ProductTable,1,FALSE)

However although this should work i dont know why it keeps coming back with #NA
????

Paul-Johnson · Mar 16th, 2002, 10:24 AM

If your range names are correct then the vlookup is accurate provided that the item is actually in the list.

Without seeing the actual data it is a little tough. Your syntax is correct. ASsuming, again, that your range names are all inclusive and the item is in the list.

PJ

Dave Patton · Mar 16th, 2002, 10:33 AM

I have created a VLOOKUP for searching for the product name in the product workbook when the product ID has been entered:

=VLOOKUP(ProductID,Products.xls!ProductTable,1,FALSE)

However although this should work i dont know why it keeps coming back with #NA

Check your syntax and datatable.

See Help for Vlookup.

Is id code in column 1 of ProductTable?
What column do you want returned?
Is the table named correctly?

You would receive a much better answer, if
you showed a very concise example of your
information.

Sami · Mar 16th, 2002, 10:40 AM

Sami · Mar 16th, 2002, 10:45 AM

Is id code in column 1 of ProductTable?
no column 3 (c)

What column do you want returned?
column 1 (a)

Is the table named correctly? I named all the columns the same as their headings and the Products table is called ProductTable

Ive got a Sales workbook set out like this:

Column A = Sale Date, Column B = Product ID,

Column C = Product Name, Column D = Price,

Column E = Quantity, Column F = Cost.

Ive got a Products workbook set out like this:

Column A = Product Name, Column B = Description

Column C = Product ID, Column D = Price,

Column E = Supplier.

In the sales workbook when the product ID is entered i want a VLOOKUP to lookup in the products workbook its name??? plz help

Paul-Johnson · Mar 16th, 2002, 10:47 AM

If what you are searching for is not in the first column, then a straight vlookup will not work.

You would wnat to integrate two functions - index and match. There is a great example in the tips section to do this.

________________
Paul

here is the link to the tip

http://www.mrexcel.com/tip021.shtml

[ This Message was edited by: Paul-Johnson on 2002-03-16 09:49 ]

Sami · Mar 16th, 2002, 10:55 AM

what do u mean not in the first column?? this can be done no matter what the column you are searching for is?? cant u??

Paul-Johnson · Mar 16th, 2002, 10:57 AM

the value you are searching for has to be in the first column of your data range to use a vlookup

Paul-Johnson · Mar 16th, 2002, 11:02 AM

For example if you had a data range like:

Column A Names
Column B Age
Column C State

The only way you could use a vlookup would be if you searched by name for age or state.

If you wanted to search a list by state and return the name or age you would have to either rearrange your data or use the index/match function.

PJ

Sami · Mar 16th, 2002, 11:08 AM

but the heatcote book doesnt do that and it works????

can you tell me how to make it work. i gave all d data

and im really pushed for time!

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

Mar 16th, 2002, 10:20 AM	#1
Sami Board Regular Join Date: Mar 2002 Posts: 122	I have created a VLOOKUP for searching for the product name in the product workbook when the product ID has been entered: =VLOOKUP(ProductID,Products.xls!ProductTable,1,FALSE) However although this should work i dont know why it keeps coming back with #NA ????

Mar 16th, 2002, 10:24 AM	#2
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	If your range names are correct then the vlookup is accurate provided that the item is actually in the list. Without seeing the actual data it is a little tough. Your syntax is correct. ASsuming, again, that your range names are all inclusive and the item is in the list. PJ

Mar 16th, 2002, 10:33 AM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	I have created a VLOOKUP for searching for the product name in the product workbook when the product ID has been entered: =VLOOKUP(ProductID,Products.xls!ProductTable,1,FALSE) However although this should work i dont know why it keeps coming back with #NA Check your syntax and datatable. See Help for Vlookup. Is id code in column 1 of ProductTable? What column do you want returned? Is the table named correctly? You would receive a much better answer, if you showed a very concise example of your information.

Mar 16th, 2002, 10:40 AM	#4
Sami Board Regular Join Date: Mar 2002 Posts: 122

Mar 16th, 2002, 10:45 AM	#5
Sami Board Regular Join Date: Mar 2002 Posts: 122	Is id code in column 1 of ProductTable? no column 3 (c) What column do you want returned? column 1 (a) Is the table named correctly? I named all the columns the same as their headings and the Products table is called ProductTable Ive got a Sales workbook set out like this: Column A = Sale Date, Column B = Product ID, Column C = Product Name, Column D = Price, Column E = Quantity, Column F = Cost. Ive got a Products workbook set out like this: Column A = Product Name, Column B = Description Column C = Product ID, Column D = Price, Column E = Supplier. In the sales workbook when the product ID is entered i want a VLOOKUP to lookup in the products workbook its name??? plz help

Mar 16th, 2002, 10:47 AM	#6
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	If what you are searching for is not in the first column, then a straight vlookup will not work. You would wnat to integrate two functions - index and match. There is a great example in the tips section to do this. ________________ Paul here is the link to the tip http://www.mrexcel.com/tip021.shtml [ This Message was edited by: Paul-Johnson on 2002-03-16 09:49 ]

Mar 16th, 2002, 10:55 AM	#7
Sami Board Regular Join Date: Mar 2002 Posts: 122	what do u mean not in the first column?? this can be done no matter what the column you are searching for is?? cant u??

Mar 16th, 2002, 10:57 AM	#8
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	the value you are searching for has to be in the first column of your data range to use a vlookup

Mar 16th, 2002, 11:02 AM	#9
Paul-Johnson New Member Join Date: Feb 2002 Posts: 47	For example if you had a data range like: Column A Names Column B Age Column C State The only way you could use a vlookup would be if you searched by name for age or state. If you wanted to search a list by state and return the name or age you would have to either rearrange your data or use the index/match function. PJ

Mar 16th, 2002, 11:08 AM	#10
Sami Board Regular Join Date: Mar 2002 Posts: 122	but the heatcote book doesnt do that and it works???? can you tell me how to make it work. i gave all d data and im really pushed for time!