Excel 2007 : How to Get Value from Left Side Cell?

**agusbagus** · 09-22-2010, 11:27 AM

Hello Excel Experts,

I need your advice on my problem.
Lets say I have data product codes and prices from 5 vendors.
(I attached the graphic for better viewer)
I want to get the lowest price from the table. I know how to do it by using formula MIN().
But my problem is I also want to get the product code from the left side the lowest price.
For example:
Row 1: Lowest=0.1 - product code=code1
Row 2: Lowest=1 - product code=code6
Row 3: Lowest=0.2 - product code=code19
Row 4: Lowest=0.15 - product code=code8

I need the data for first and second columns that I marked the header as red in the illustration picture I attached.

What formula should I use to achieve that??
I have tried googling many days, but still have no luck with the formula.
Any helping will be deeply appreciate.

Thanks & Regards,
Agus

**NBVC** · 09-22-2010, 11:36 AM

Try:

=Index(B2:B5,Match(Min(C2:C5),C2:C5,0))

you can replace MIN(C2:C5) with cell containing your original MIN formula.

**agusbagus** · 09-23-2010, 06:22 AM

Originally Posted by NBVC

Try:

=Index(B2:B5,Match(Min(C2:C5),C2:C5,0))

you can replace MIN(C2:C5) with cell containing your original MIN formula.

Hello NVBC,

Sorry, probably I dont clearly explain what I need.
So basically I need to find the lowest price per row (at column C in the picture I use MIN function) and then get the product code from the left side of the lowest price (then insert it to column B).
I believe if I can get for the first row, I can drag and drop for whole rows.
How do I do that?

Thanks for your further advice.

regards,
Agus

**Marcol** · 09-23-2010, 06:51 AM

Hi,
You would do best to post a sample workbook rather than a picture.

It should clearly illustrate your problem and not contain any sensitive data.

**NBVC** · 09-23-2010, 07:59 AM

Okay, in B2:

=INDEX(D2:M2,MATCH(C2,D2:M2,0)-1) copied down

in A2:

=INDEX($D$1:$M$1,MATCH(C2,D2:M2,0)-1)

or

=INDEX($D$1:$M$1,MATCH(B2,D2:M2,0))

**agusbagus** · 09-23-2010, 08:04 AM

Originally Posted by Marcol

Hi,
You would do best to post a sample workbook rather than a picture.

It should clearly illustrate your problem and not contain any sensitive data.

Hi Marcol, thanks for your advice.. I have attached the file with file + instruction inside.
Hopefully make everything clear.

Cheers,
Agus

**NBVC** · 09-23-2010, 08:07 AM

As per my last suggestion try:

=INDEX(C2:N2,MATCH(B2,C2:N2,0)-1)

copied down.

**agusbagus** · 09-23-2010, 08:36 AM

Originally Posted by NBVC

As per my last suggestion try:

=INDEX(C2:N2,MATCH(B2,C2:N2,0)-1)

copied down.

Hi Moderator, u r brilliant. A ton of thanks for you

Excel 2007 : How to Get Value from Left Side Cell?

LinkBack

Thread Tools

Rate This Thread

Display

How to Get Value from Left Side Cell?

Re: How to Get Value from Left Side Cell?

Re: How to Get Value from Left Side Cell?

Re: How to Get Value from Left Side Cell?

Re: How to Get Value from Left Side Cell?

Re: How to Get Value from Left Side Cell?

Re: How to Get Value from Left Side Cell?

Re: How to Get Value from Left Side Cell?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions