Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Function (Excel 2000)

    I've added some prices

    Thanks

    Mario
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Aladin
    Attached Files Attached Files
    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •