Match supplier name

**bbthaa** · 06-07-2011, 08:16 AM

Dear All,

I want to match a supplier name that is "crown computer" from column A of 1st spreadsheet with column A of 2nd spreadsheet and return value from column B of 2nd spreadsheet.

The problem is supplier name that is "crown computer" is appearing i think 500 times in column A of 1st spreadsheet and in column A of 2nd spread sheet

Please help formula not working

Regards

Ali

**NBVC** · 06-07-2011, 08:19 AM

So for each occurance of the supplier ID in Sheet1, you want a different match from Sheet2?

Are there the same number of matches?

If not, what happens if there are more in Sheet1 than in Sheet2 or vice versa?

**bbthaa** · 06-07-2011, 08:35 AM

Dear Sir,

Yes, there are the same number of matches, means one amount appearing against all occurance.

Regards,

Ali

**NBVC** · 06-07-2011, 08:41 AM

I think the easiest way is to use a helper column on Sheet2 to identify the consecutive number of each supplier

So, for example if your suppliers in Sheet2 are in column A and the data you want to get is in column B, then in C2, try:

=A2&"_"&COUNTIF(A$1:A2,A2)

copied down.

Then in Sheet1, your formula in B2 to get the related information would be:

=INDEX(Sheet2!B:B,MATCH($A2&"_"&COUNTIF($A$1:$A2,$A2),Sheet2!$C:$C,0))

where Sheet2 is the name of your reference sheet. copied down.