HELP ON VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

| 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,
 
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)))
 
Back
Top