VLookup

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

Guest

Hello,
I am creating a sales order form for my boss. He would like me to have a
drop down box for the customer name and then would like for the customer # to
automatically pop-up in the box below it.

So I am using a VLookup but can't get it to work. I did try reversing it. So
I put the customer # in, and then it found the customer name, but it will not
work the other way around like the boss wants it to.

Any suggestions?
 
If your lookup table is like this:

Cust ID Name
000121 Joe Smith
000345 Fred Bloggs

etc, then VLOOKUP will not work, as the lookup value needs to be in
the left hand column of the table. You could duplicate the Customer ID
column like this:

Cust ID Name Cust ID
000121 Joe Smith 000121
000345 Fred Bloggs 000345

then use VLOOKUP on the name, or you can use an INDEX/MATCH
combination, which will allow you to get the ID from the left of your
name, as in the first table.

Hope this helps.

Pete
 
With Vlookup(), the lookup value must be to the left of the lookup array
containing the values your looking to return.
So, in your case, the name should be in Column A, and the customer number
must be in any column to the right.
If your datalist has the number in the left most column, then you'll have to
use the Index - Match combination.

You index the column that you want returned (number col.), and use Match to
find the pertinent row in that column, which references the column
containing the name.

For example:

=Index(C1:C100,Match(K1,D1:D100,0))
 
How was I missing that!! Thank you!

RagDyer said:
With Vlookup(), the lookup value must be to the left of the lookup array
containing the values your looking to return.
So, in your case, the name should be in Column A, and the customer number
must be in any column to the right.
If your datalist has the number in the left most column, then you'll have to
use the Index - Match combination.

You index the column that you want returned (number col.), and use Match to
find the pertinent row in that column, which references the column
containing the name.

For example:

=Index(C1:C100,Match(K1,D1:D100,0))
Click to expand...
 
Back
Top