Matching Item numbers in two different worksheets using item description

**lidoxy** · 07-26-2011, 09:02 PM

Hi,

I'm trying to match item numbers in the inventory tab with the item numbers in the BT tab using the description title as the common thing. I have added a column called BT Itme number on Inventory tb as this is where i want to display the item number from BT tab.

Problem is the description details in Inventory tab is different from the description in BT tab.

I'm trying to get a formula that allows e to do this without doing it row by row (item by item) like i have done on cell D34 & D49 on Inventory tab.

Appreciate any assistance.

Regards,
Lidoxy

**jeffreybrown** · 07-26-2011, 09:07 PM

How about...

=INDEX(BT!$A$2:$A$255,MATCH(C34,BT!$B$2:$B$255,0))

What version of Excel are you using?

If 2007 you can use Iferror to return a zero

=IFERROR(INDEX(BT!$A$2:$A$255,MATCH(C34,BT!$B$2:$B$255,0)),0)

2003

=IF(ISNA(INDEX(BT!$A$2:$A$255,MATCH(C34,BT!$B$2:$B$255,0))),0,INDEX(BT!$A$2:$A$255,MATCH(C34,BT!$B$2:$B$255,0)))

**lidoxy** · 07-26-2011, 09:22 PM

Thanks Jeff.

I'm using excel 2003.

With the 1st formula, i got #NA, with the 2nd formula i got #Name? and with the 3rd formula i got 0. Only cell D6 and D34 return a value that's close to what i am looking for (486), but this is the item number for the corona.

Thanks,
Lidoxy

**jeffreybrown** · 07-26-2011, 09:34 PM

What's wrong with this.

Please tell me what you expect that is not here?

**lidoxy** · 07-26-2011, 10:25 PM

Originally Posted by jeffreybrown

What's wrong with this.

Please tell me what you expect that is not here?

Thanks Jeff - nothing is wrong with the formulae. Just wondering if there's anything i can do to make say cell D14 return value 281 for the sam adams lager description. Or do the descriptions have to have the same characters?

Thanks again,
Lidoxy

**jeffreybrown** · 07-26-2011, 10:35 PM

Yes they need to match.

You could try Fuzzy Matching

I've never actually used this for my purposes, but I know it works.

You may have to play with the layout of your data, but if you get stuck maybe either somebody here has some ideas or you can reply to the thread above.