# Vlookup, Index, Match Query

#### JV0710

##### Active Member
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

Thanks

JVN

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Domenic

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

Code:
``=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

##### Active Member
Hi Domenic

Thank you . . . This helps.

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

##### Active Member
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
ABCDEFGHIJKLM
1LowestPriceSupplierPriceRefno:Supplier1Supplier2Supplier3Supplier4Supplier5Supplier6Supplier7Supplier8
2A2Supplier340A12021002220.5230
3A245424000044.50
4A33938.50390000
5A4005353.5520054
6A502423.750022.75024.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
Code:
``=IF(A2<>0,SMALL(E3:M3,(1+COUNTIF(E3:M3,0))),"")``

In B2 - The Supplier
Code:
``=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

##### MrExcel MVP
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

##### Active Member
Thank you Sankar

This solves my problem

I appreciate your help

Regards

JVN

#### Domenic

##### MrExcel MVP
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

##### Active Member
Thank you Domenic

This works great

I appreciate the help

Thanks again

JVN

#### Domenic

##### MrExcel MVP
You're very welcome!

Replies
6
Views
386
Replies
2
Views
216
Replies
2
Views
242
Replies
0
Views
477
Replies
1
Views
179

1,191,274
Messages
5,985,693
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back