ExcelBanter - HELP ON VLOOKUP

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - HELP ON VLOOKUP (https://www.excelbanter.com/excel-discussion-misc-queries/166090-help-vlookup.html)

I have made a form and inserted VLookups. In my list of products there is
more than one of that product name but different sizes. So it looks like this:

Product name Item # Discription Unit of measure
Buccaneer Plus #00521 2x2.5ga ju ga
Buccaneer Plus #00522 30ga drum ga
Buccaneer Plus #00523 Bulk ga ga

So then I made a drop down list from the product name and everything else
fills in itself because of the vlookups I put in.

The problem is it only recognizes the first product name and number.

How can I fix this? What should I do different?

Hi Bonnie,

Concatenate the Product name and Item#, both in the lookup table and in the search argument

Concatenation can be done with the "&" operator or with the CONCATENATE() function

Post again in this thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bonnie" wrote in message ...
| I have made a form and inserted VLookups. In my list of products there is
| more than one of that product name but different sizes. So it looks like this:
|
| Product name Item # Discription Unit of measure
| Buccaneer Plus #00521 2x2.5ga ju ga
| Buccaneer Plus #00522 30ga drum ga
| Buccaneer Plus #00523 Bulk ga ga
|
| So then I made a drop down list from the product name and everything else
| fills in itself because of the vlookups I put in.
|
| The problem is it only recognizes the first product name and number.
|
| How can I fix this? What should I do different?
|
|

Hi,

I need to see your VLOOKUP formula.

Thanks,
--
Farhad Hodjat

"Bonnie" wrote:

I have made a form and inserted VLookups. In my list of products there is
more than one of that product name but different sizes. So it looks like this:

Product name Item # Discription Unit of measure
Buccaneer Plus #00521 2x2.5ga ju ga
Buccaneer Plus #00522 30ga drum ga
Buccaneer Plus #00523 Bulk ga ga

So then I made a drop down list from the product name and everything else
fills in itself because of the vlookups I put in.

The problem is it only recognizes the first product name and number.

How can I fix this? What should I do different?

Niek, can you explain in simpler terms? I am not sure I followed what you said.

"Niek Otten" wrote:

Hi Bonnie,

Concatenate the Product name and Item#, both in the lookup table and in the search argument

Concatenation can be done with the "&" operator or with the CONCATENATE() function

Post again in this thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bonnie" wrote in message ...
| I have made a form and inserted VLookups. In my list of products there is
| more than one of that product name but different sizes. So it looks like this:
|
| Product name Item # Discription Unit of measure
| Buccaneer Plus #00521 2x2.5ga ju ga
| Buccaneer Plus #00522 30ga drum ga
| Buccaneer Plus #00523 Bulk ga ga
|
| So then I made a drop down list from the product name and everything else
| fills in itself because of the vlookups I put in.
|
| The problem is it only recognizes the first product name and number.
|
| How can I fix this? What should I do different?
|
|

Here it is,

=VLOOKUP(A17,'PRODUCT INFO'!A2:D674,2,FALSE)

"Farhad" wrote:

Hi,

I need to see your VLOOKUP formula.

Thanks,
--
Farhad Hodjat

"Bonnie" wrote:

I have made a form and inserted VLookups. In my list of products there is
more than one of that product name but different sizes. So it looks like this:

Product name Item # Discription Unit of measure
Buccaneer Plus #00521 2x2.5ga ju ga
Buccaneer Plus #00522 30ga drum ga
Buccaneer Plus #00523 Bulk ga ga

So then I made a drop down list from the product name and everything else
fills in itself because of the vlookups I put in.

The problem is it only recognizes the first product name and number.

How can I fix this? What should I do different?

Bonnie:

the company is closing and i have to step out i will get back to you
tomorrow so i need to know what is A17 in your formula is it the drop down
list? how did you make it? is in datavalidation? or...

Thanks,
--
Farhad Hodjat

"Bonnie" wrote:

Here it is,

=VLOOKUP(A17,'PRODUCT INFO'!A2:D674,2,FALSE)

"Farhad" wrote:

Hi,

I need to see your VLOOKUP formula.

Thanks,
--
Farhad Hodjat

"Bonnie" wrote:

I have made a form and inserted VLookups. In my list of products there is
more than one of that product name but different sizes. So it looks like this:

Product name Item # Discription Unit of measure
Buccaneer Plus #00521 2x2.5ga ju ga
Buccaneer Plus #00522 30ga drum ga
Buccaneer Plus #00523 Bulk ga ga

So then I made a drop down list from the product name and everything else
fills in itself because of the vlookups I put in.

The problem is it only recognizes the first product name and number.

How can I fix this? What should I do different?

I'm not Niek, but maybe this'll help...

You have 2 dropdowns, right--one for the product name and one for the Item
number?

On your worksheet with the lookup table (I used Sheet2), insert a new column A.
Put this in A2 (headers in row 1):
=B2&"---"&C2
(and drag down)

You'll end up with a table that looks like:
Product/Item# Product name Item # Discription Unit of measure
Buccaneer Plus---#00521 Buccaneer Plus #00521 2x2.5ga ju ga
Buccaneer Plus---#00522 Buccaneer Plus #00522 30ga drum ga
Buccaneer Plus---#00523 Buccaneer Plus #00523 Bulk ga ga

Then you can use that new column A to in your =vlookup() formula.

Say you have your product name in A1 and B1 of Sheet1:

=vlookup(a1&"---"&b1,sheet2!a:e,4,false)

or maybe one of these to check for errors:

both A1 and B1 have to be filled in for this one:
=if(counta(a1:b1)<2,"",vlookup(a1&"---"&b1,sheet2!a:e,4,false))

or

so both A1:B1 have to be filled in and it looks to see if there's a match
in column A of the lookup table:
=if(counta(a1:b1)<2,"",if(iserror(vlookup(a1&"---"&b1,sheet2!a:e,4,false)),"",
vlookup(a1&"---"&b1,sheet2!a:e,4,false)))

Bonnie wrote:

Niek, can you explain in simpler terms? I am not sure I followed what you said.

"Niek Otten" wrote:

Hi Bonnie,

Concatenate the Product name and Item#, both in the lookup table and in the search argument

Concatenation can be done with the "&" operator or with the CONCATENATE() function

Post again in this thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bonnie" wrote in message ...
| I have made a form and inserted VLookups. In my list of products there is
| more than one of that product name but different sizes. So it looks like this:
|
| Product name Item # Discription Unit of measure
| Buccaneer Plus #00521 2x2.5ga ju ga
| Buccaneer Plus #00522 30ga drum ga
| Buccaneer Plus #00523 Bulk ga ga
|
| So then I made a drop down list from the product name and everything else
| fills in itself because of the vlookups I put in.
|
| The problem is it only recognizes the first product name and number.
|
| How can I fix this? What should I do different?
|
|

--

Dave Peterson