Formula to tag list of Items with Grouping Number

1. Formula to tag list of Items with Grouping Number

I have a list of items that are all coded (1500 or more items)
eg BEBSHD6 , FOAT122 , HWTD5 etc
The first two letters of each code denotes what catagory or group the item belongs to
I have another list which has the two letter codes of all the 34 different item groups
eg BE , FO , HW etc
In the column next to these two letter codes is a number (1 to 35)
eg BE next to 24 FO next to 1 HW next to 12 etc

I am trying to create a formula in excel that will look at the first two letters of the item codes
Then find these first two letters in the other list (the two letter groups list)
Then tell me what the number is next to that two letter code

This number has to be displayed next to the item code on the first list

I Have tried lookup using LOOKUP(LEFT(A2,2) etc jsut can't get it to work
I tried LOOKUP(LEFT(A2,2) and then if it equals the two letter code show me the number next to it
But it only works for seven looks (my beginner terminology)

Thanks
Rob

2. Re: Help with this Formula PLEASE

Hi,

I may as well tell you before the inevitable post from a Moderator that you will have to change the title of your thread to something less generic to comply with the Forum Rules before anyone can help you.

Regards

3. Re: Formula to tag list of Items with Grouping Number

try vlookup

=vlookup(left(A2,2),C1:D100,2,false)
false-> exact match
true->appro. match
eg.

5. Re: Formula to tag list of Items with Grouping Number

If you have no further query regarding this thread you can mark it as solved.

Thanks.

6. Re: Formula to tag list of Items with Grouping Number

C1:D100 Changes as I drag down the formula
eg C2:D101 , C3:D102

How to stop this ?

Rob

7. Re: Formula to tag list of Items with Grouping Number

=vlookup(left(A2,2),\$C\$1:\$D\$100,2,false)

8. Re: Formula to tag list of Items with Grouping Number

Thank you all
Problem solved

9. Re: Formula to tag list of Items with Grouping Number

CLICK the STAR icon on the left side below their user name to say thank you to those who had helped you. Thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1