Vlookup, Index, Match Query

[JV0710]

Good day

Please can I get help with the following query:

In my workbook, On sheet1, from Column E ( Row 2) . . .

In Column E, I have Reference Numbers ( e.g DCB01 ) - About 500 Lines
In Column F:AF, (Row 1) - I have Supplier Names
In Column F : AF (Rows 2:500) - I have Prices.


On sheet 2, in Column A, I am entering the reference Number, and in Column B, I would like to see the Name of the Supplier with the lowest price from Sheet 1 - for the reference number

I have been trying combinations of Vlookup, Index and Match - But I just cannot get it to work

Please help

Thanks

JVN

 

[Domenic]

Assuming that A2 contains the reference number of interest, try...

=INDEX($F$1:$AF$1,MATCH(MIN(INDEX($F$2:$AF$500,MATCH(A2,$E$2:$E$500,0),0)),INDEX($F$2:$AF$500,MATCH(A2,$E$2:$E$500,0),0),0))

Add the sheet reference, accordingly.

Hope this helps!

 

[JV0710]

Hi Domenic

Thank you . . . This helps.

Please may I had add another complication though -

Some of the columns from F to AF ( Suppliers ) have zero's. ( Not All suppliers have prices listed)

So, in column A - I will enter the reference number.
In column B - I want to find the supplier with lowest price > 0.
and in column C - I want to see the actual lowest price for the supplier found in column B.

Sorry to add this complication now and not yesterday - I had to leave the office and I guess looking at it after a break, made me view it differently.

Thanks for your help - It is much appreciated

JVN

 

[JV0710]

Here is a visual of What I am trying to get a formula for:

In Cell A2, I enter the ref no:
In Cell B2, I need a formula to return The supplier with the lowest price - excluding Zero's ( In this example - Supplier 3 )
In Cell C2, I need a formula to return the actual lowest price ( In this example - 40 )

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		LowestPriceSupplier	Price		Refno:	Supplier1	Supplier2	Supplier3	Supplier4	Supplier5	Supplier6	Supplier7	Supplier8
2	A2	Supplier3	40		A1	20	21	0	0	22	20.5	23	0
3					A2	45	42	40	0	0	0	44.5	0
4					A3	39	38.5	0	39	0	0	0	0
5					A4	0	0	53	53.5	52	0	0	54
6					A5	0	24	23.75	0	0	22.75	0	24.5
7
Sheet1

If I know the row that the ref no: is in, Then I can work it out with the following formulae:

In C2 - The lowest price

=IF(A2<>0,SMALL(E3:M3,(1+COUNTIF(E3:M3,0))),"")

In B2 - The Supplier

=IF(A2<>0,INDEX(E1:M7,1,MATCH(C2,E3:M3,0)),"")

But as this lookup work will be on a different sheet, and I will not know the row number ( there are up to 500 rows ), I need to have a different formula that will find these values.

I will appreciate any help with this please

Thanks Again

JVN

 

[sanrv1f]

a small change in Domenic's formula to find the supplier with lowest non-zero item for the given product (use Ctrl+Shift+Enter to commit) in B2

=INDEX($F$1:$AF$1,MATCH(MIN(INDEX(IF($F$2:$AF$500=0,MAX($F$2:$AF$500),$F$2:$AF$500),MATCH(A2,$E$2:$E$500,0),0)),INDEX($F$2:$AF$500,MATCH(A2,$E$2:$E$500,0),0),0))

in C2 enter the below formula in C2 to find the lowest non-zero item for the given product

=VLOOKUP(A2,$E$2:$AF$500,MATCH(B2,$E$1:$AF$1,0),0)

 

[JV0710]

Thank you Sankar

This solves my problem

I appreciate your help

Regards

JVN

 

[Domenic]

Try...

C2, confirmed with CONTROL+SHIFT+ENTER:

=MIN(IF(INDEX($F$2:$AF$500,MATCH(A2,$E$2:$E$500,0),0)>0,INDEX($F$2:$AF$500,MATCH(A2,$E$2:$E$500,0),0)))

B2:

=INDEX($F$1:$AF$1,MATCH(C2,INDEX($F$2:$AF$500,MATCH(A2,$E$2:$E$500,0),0),0))

Hope this helps!

 

[JV0710]

Thank you Domenic

This works great

I appreciate the help

Thanks again

JVN

 

[Domenic]

You're very welcome!