MrExcel Publishing
Your One Stop for Excel Tips & Solutions

V-LOOKUP


Posted by JPT217 on April 09, 2001 11:10 AM

I have two columns of information the first column can have multiples of the same # (These are customer #'S). The other column is being imported from are system they are also customer numbers. Next to the second column there is a column containing letters. Telling what type of customer. I would like to have a lookup that looks up the numbers in the first column and when it is found puts the letter in the column next to it saying what type of customer it is. Please help!!!


Posted by Aladin Akyurek on April 09, 2001 11:31 AM

Care to provide a snippet of your real data if possible? Here is how: Activate an empty cell, enter =, select the relevant but a small portion of data, then hit control+shift+enter at the same time. You'll see at the formula bar a range put between 2 braces {}. Now: select the range in the formula and hit F9. Copy the resulting array and paste it into your post.

Aladin

Posted by Gareth on April 10, 2001 5:22 AM

- Again, lets see some data. I would suggest using the CONCATENATE function from what I understand to relate your second column imported with the type of customer.

Posted by JPT217 on April 10, 2001 5:46 AM


{"Customer","Number","Type Of Customer",0,0,0,"Imported Date",0;0,0,0,0,0,0,0,0;"General Data Co",481,0,0,0,0,"Customer #","Type";"NCS Health Care",345,0,0,0,0,345,"E";"Madison Precision",884,0,0,0,0,884,"R";"PC Connection",778,0,0,0,0,778,"S";"General Data Co",481,0,0,0,0,481,"R";0,0,0,0,0,0,345,"E"}

Hope I did this right

Posted by Aladin Akyurek on April 10, 2001 1:24 PM

: I have two columns of information the first column can have multiples of the same # (These are customer #'S). The other column is being imported from are system they are also customer numbers. Next to the second column there is a column containing letters. Telling what type of customer. I would like to have a lookup that looks up the numbers in the first column and when it is found puts the letter in the column next to it saying what type of customer it is. Please help!!!

And I hope I got it right. It seems you picked an area of 8 x 7.

I'd suggest that you select all cells under the column headings Customer # and Type (excluding these column headings) and name the selected range TYPES.

Activate the first empty cell under Type of Customer and enter the following VLOOKUP formula whose first argument is the cell immediately left of the cell where you enter the formula (It's a Number):

=VLOOKUP(B2,TYPES,2,0)

where B2 contains "Number" of the "Customer".

Copy down this formula for all customers under "Number".

Hope this helps.

Aladin

Posted by JPT217 on April 11, 2001 5:18 AM

IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!! THANKS ALADIN

i