# Thread: Lookup Cheapest (Excel XP)

1. ## Lookup Cheapest (Excel XP)

Hi,

How can I get VLookup to pull out the cheapest price of a particulat product supplied by 3 or 4 different suppliers. Vlookup will find a match on a product code, but there may be 4 of that code. Once found, I need the cheapest product price to be returned for that product code.

Thanks for any assistance.
See the attachment for sample.

2. ## Re: Lookup Cheapest (Excel XP)

You don't use VLookup for this. Use this array formula (confirm with Ctrl+Shift+Enter):

=MIN(IF(Sheet2!\$A\$2:\$A\$5=A2,Sheet2!\$D\$2:\$D\$5))

Fill down as far as needed.

3. ## Re: Lookup Cheapest (Excel XP)

I'll give it a try. TX Hans!

4. ## Re: Lookup Cheapest (Excel XP)

TX Hans,

The formula works well. (See the datafile attached).
I also need the supplier code collected from sheet2. Since this is not a number I figured that we can use the min function you sent me to collect the cheapest price and then on that same line offset to collect the supplier code for that record. Any ideas as to how to offset to collect the supplier code into sheet 1?
Tx

5. ## Re: Lookup Cheapest (Excel XP)

If you will only ever have one match per product, you can use:
<code>=INDEX(Sheet2!\$B\$2:\$B\$5,SUMPRODUCT((Sheet2!\$ A\$2:\$A\$5=Sheet1!A2)*(Sheet2!\$D\$2:\$D\$5=Sheet1!D2)*( ROW(Sheet2!\$D\$2:\$D\$5)-1)))</code>

6. ## Re: Lookup Cheapest (Excel XP)

Probably safer to use this array formula:
<code>=INDEX(Sheet2!\$B\$2:\$B\$5,MIN(IF((Sheet2!\$A\$2: \$A\$5=Sheet1!A2)*(Sheet2!\$D\$2:\$D\$5=Sheet1!D2),(ROW( Sheet2!\$D\$2:\$D\$5)-1),"")))</code>

and the full version with error checking in case of no matches:
=IF(COUNTIF(Sheet2!\$A\$2:\$A\$5,Sheet1!A2)=0,"",INDEX (Sheet2!\$B\$2:\$B\$5,MIN(IF((Sheet2!\$A\$2:\$A\$5=Sheet1! A2)*(Sheet2!\$D\$2:\$D\$5=Sheet1!D2),(ROW(Sheet2!\$D\$2: \$D\$5)-1),""))))
which also needs to be array-entered.

7. ## Re: Lookup Cheapest (Excel XP)

Wow Rory...
I wont even go there to ask how you came out to such a solutuion!

Many tx to you and Hans.

Cheers

#### Posting Permissions

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