VLOOKUPS - One item with two descriptions

[agerrard]

Hi All,

Im running Excel 2003. My problem is to do with the below. I want my vlookup to search by the item description (so in this case product 1). Normally it would be fine to do this, but for this product it has 2 supplier values associated to it (the product was made by supplier a last year, but now its made by supplier b).

I want my vlookup to return the values in the 2nd row (so i want it to take "supplier b" and the value in year 2 of $11,832) i dont care about the values in year 1.

So is there a way to do this? I cant change the way that the data is coded in the raw data file so i have to try this way instead.

Hope this makes sense.

Cheers!

Vlookup Example.xls
	A	B	C	D
4	Item	Supplier	Year1	Year2
5	Product1	SupplierA	$9,389.04	$-
6		SupplierB	$-	$11,832.27
Sheet1

 

[Yogi Anand]

Hi agerrard:

How about posting a few more rows of your data, so we can clearly see how your data is laid out ... and then let us take it from there

 

[agerrard]

ok give me a minute or two!

 

[agerrard]

OK here is a little bit more of the example:

Basically i have hundreds of products and i need to match each product by supplier (as well as other variables)...

Vlookup Example.xls
	A	B	C	D
4	Item	Supplier	Year1	Year2
5	Product1	SupplierA	$9,389.04	$-
6		SupplierB	$-	$11,832.27
7	Product2	SupplierC	$622.19	$765.75
8	Product3	SupplierD	$-	$756.22
9	Product4	SupplierA	$-	$741.95
10	Product5	SupplierD	$793.43	$655.31
11		SupplierB	$82.67	$-
12	Product6	SupplierE	$408.34	$620.38
Sheet1

 

[PaddyD]

is max # of suppliers 2 or might there be more ?

 

[agerrard]

thanks for your question.

the max is always 2 there are never more than 3 suppliers associated with a product over the year.