# Thread: Lookup Function (Excel 2000)

1. ## Lookup Function (Excel 2000)

I need some help on an formula/array. I have a sheet for ordering stock. In column B I will be selecting the stock item. In column C I would like to show the supplier with the cheapest price. No the drop down list are taken from the stock sheet. The Stock sheet is listed by product and with the suppliers at the top. So if I select Product A then it should report the supplier name with the cheapest price. Refer to attached workbook.

I know that Access would be the answer BUT the budget doesn't allow it.

Thanks

Mario

2. ## Re: Lookup Function (Excel 2000)

Mario,

Could you fill the Stocksheet sheet with some (dummy) prices? That will make it easier to create a formula.

Regards,
Hans

Thanks

Mario

4. ## Re: Lookup Function (Excel 2000)

Insert an additional column after B in your stock sheet and in C2 enter and copy down:

=IF(B2,MATCH(B2,E2:Z2,0)+CELL("Col",\$D\$1),"")

Hide column C.

In B2 in Supply Order Form enter and copy down:

=IF(LEN(B12),INDEX(Stocksheet!\$A\$1:\$Z\$1,VLOOKUP(B1 2,Stocksheet!A:C,3,0)),"")

See the attachment.

5. ## Re: Lookup Function (Excel 2000)

Thanks it works great. I knew the Lounge will be able to assist. The formula you were questioning: I deleted a few sheets and it refered to a calculation on there.

Mario

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•