Locate Origin of Min Result Value and Return Other value

**Fozzy** · 07-10-2012, 02:48 AM

I have a Workbook with various supplier pricing on seperated sheets.

On the primary sheet, Certain cells pull the values from the supplier sheets using =MIN.
This is resulting in the Minimum value being displayed.

What I now need is a formula, that can locate which sheet the value originates and then return either, the sheet name OR Cell B1's value from that Sheet.

Working 5.11 .xlsx

**Ace_XL** · 07-10-2012, 03:19 AM

If its only 2 sheets, use the below in E4 and adapt accordingly

=IFERROR(INDEX(Sheet2!B4,MATCH(C4,Sheet2!C4,0)),INDEX(Sheet3!B4,MATCH(C4,Sheet3!C4,0)))

**Fozzy** · 07-10-2012, 04:53 AM

Ace,

Thanks for your reply,

There are several tabs, and are listed as such.

MASTER - where the formula needs entering

Altus
BlueJay
QML
Samy
SCL
UniTrans
Veritas
Waterlink.

I can send you a more complete file if you like, but the forum wont let me upload it.

REgards

Tony

**Fozzy** · 07-10-2012, 06:49 AM

So,

Was just playing with the above formula and expanding it to fit the names of my tabs so that it looks similar to this....

[B]=IFERROR(INDEX(ALTUS!B4,MATCH(F4,ALTUS!D4,0)),(INDEX(BLUEJAY!B4,MATCH(F4,BLUEJAY!D4,0)),(INDEX(QML!B4,MATCH(F4,QML!D4,0)),(INDEX(SAMY!B4,MATCH(F4,SAMY!D4,0)),(INDEX(SCL!B4,MATCH(F4,SCL!D4,0)),(INDEX(UNITRANS!B4,MATCH(F4,UNITRANS!D4,0)),(INDEX(VERITAS!B4,MATCH(F4,VERITAS!D4,0)),(INDEX(WATERLINK!B4,MATCH(F4,WATERLINK!D4,0))))))))

However, it came up with an error.

Just to clarify, B1 is a constant value that should be returned if the value on Master F4 Is the same as any sheets D4.....

Appreciate any and all feedback... I have been at this one for a couple of weeks now.

**Ace_XL** · 07-10-2012, 07:08 AM

Minor error... at each potential error condition you would need an IFERROR.

Hence, use below

=IFERROR(INDEX(Altus!B4,MATCH(F4,Altus!D4,0)),IFERROR(INDEX(Bluejay!B4,MATCH(F4,Bluejay!D4,0)),IFERROR(INDEX(QML!B4,MATCH(F4,QML!D4,0)),IFERROR(INDEX(Samy!B4,MATCH(F4,Samy!D4,0)),IFERROR(INDEX(SCL!B4,MATCH(F4,SCL!D4,0)),IFERROR(INDEX(Unitrans!B4,MATCH(F4,Unitrans!D4,0)),IFERROR(INDEX(Veritas!B4,MATCH(F4,Veritas!D4,0)),IFERROR(INDEX(Waterlink!B4,MATCH(F4,Waterlink!D4,0)),""))))))))

**Fozzy** · 07-10-2012, 07:27 AM

AWESOME!!!!

Now I just have to copy and amend it into 380rows on 10 columns!

Thank you Bro!

**Fozzy** · 07-10-2012, 07:30 AM

Is there anyway to "drag" this....

All the values increase, whereas I need B1 to remain the same

**Ace_XL** · 07-10-2012, 08:16 AM

On re-reading the post, I see that you always want B1 of the relevant sheet returned. So this should do it...

=IFERROR(INDEX(Altus!$B$1,MATCH(F4,Altus!D4,0)-1),IFERROR(INDEX(Bluejay!$B$1,MATCH(F4,Bluejay!D4,0)-1),IFERROR(INDEX(QML!$B$1,MATCH(F4,QML!D4,0)-1),IFERROR(INDEX(Samy!$B$1,MATCH(F4,Samy!D4,0)-1),IFERROR(INDEX(SCL!$B$1,MATCH(F4,SCL!D4,0)-1),IFERROR(INDEX(Unitrans!$B$1,MATCH(F4,Unitrans!D4,0)-1),IFERROR(INDEX(Veritas!$B$1,MATCH(F4,Veritas!D4,0)-1),IFERROR(INDEX(Waterlink!$B$1,MATCH(F4,Waterlink!D4,0)-1),""))))))))

and drag down. For dragging across I suggest you keep all columns similar to your source sheets i.e. in Master sheet keep all cost columns next to each other as they are in the source sheets

Let me know if it works!