Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Steve R
 
Posts: n/a
Default lookup - return minimum value

I am looking for a formula to return the minimum value from a table (below)

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5

If the following lookup info is entered into 4 cells(below)

PET Red Blue Yellow - The value "3" is returned since it is the minimum
value in the "PET row" of the 3 items listed

Thanks for any help
Steve
  #2   Report Post  
Max
 
Posts: n/a
Default

One try ..

Assuming this table is in A1:F4
Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5


and this "criteria range" is in A6:D6
PET Red Blue Yellow


Put in say, E6:

=MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0))))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steve R" wrote in message
...
I am looking for a formula to return the minimum value from a table

(below)

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5

If the following lookup info is entered into 4 cells(below)

PET Red Blue Yellow - The value "3" is returned since it is the minimum
value in the "PET row" of the 3 items listed

Thanks for any help
Steve



  #3   Report Post  
Alan Beban
 
Posts: n/a
Default

Another try with the same layout:

=MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered

Alan Beban

Max wrote:
One try ..

Assuming this table is in A1:F4

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5



and this "criteria range" is in A6:D6

PET Red Blue Yellow



Put in say, E6:

=MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0))))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steve R" wrote in message
...

I am looking for a formula to return the minimum value from a table


(below)

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5

If the following lookup info is entered into 4 cells(below)

PET Red Blue Yellow - The value "3" is returned since it is the minimum
value in the "PET row" of the 3 items listed

Thanks for any help
Steve




  #4   Report Post  
Steve Rolls
 
Posts: n/a
Default

Thanks to both of you. Both or your solutions worked. Thank You

I now have a followup question Is there a way for the fomula to
work if the criteria row only has one, two values (in b6:d6) ?
(of course there needs to be criteria in A6)
With the formula Max gave, if 3 criteria are not entered I get
#N/A.

Steve


--
Steve
"Alan Beban" wrote in message
...
Another try with the same layout:

=MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered

Alan Beban

Max wrote:
One try ..

Assuming this table is in A1:F4

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5



and this "criteria range" is in A6:D6

PET Red Blue Yellow



Put in say, E6:

=MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0))))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steve R" wrote in message
...

I am looking for a formula to return the minimum value from a table


(below)

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5

If the following lookup info is entered into 4 cells(below)

PET Red Blue Yellow - The value "3" is returned since it is the minimum
value in the "PET row" of the 3 items listed

Thanks for any help
Steve




  #5   Report Post  
Domenic
 
Posts: n/a
Default

Try the following array formula...

=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B2:F2)),ROW(
INDIRECT("1:"&COUNTA(B6:D6)))),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Steve Rolls" wrote:

Thanks to both of you. Both or your solutions worked. Thank You

I now have a followup question Is there a way for the fomula to
work if the criteria row only has one, two values (in b6:d6) ?
(of course there needs to be criteria in A6)
With the formula Max gave, if 3 criteria are not entered I get
#N/A.

Steve


--
Steve
"Alan Beban" wrote in message
...
Another try with the same layout:

=MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered

Alan Beban

Max wrote:
One try ..

Assuming this table is in A1:F4

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5


and this "criteria range" is in A6:D6

PET Red Blue Yellow


Put in say, E6:

=MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0))))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steve R" wrote in message
...

I am looking for a formula to return the minimum value from a table

(below)

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5

If the following lookup info is entered into 4 cells(below)

PET Red Blue Yellow - The value "3" is returned since it is the minimum
value in the "PET row" of the 3 items listed

Thanks for any help
Steve




  #6   Report Post  
Domenic
 
Posts: n/a
Default

Correction...

=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B1:F1)),ROW(
INDIRECT("1:"&COUNTA(B6:D6)))),0))

....confirmed with CONTROL+SHIFT+ENTER.

Note that the change is not really significant and my previous formula
works just the same.

Hope this helps!

In article ,
Domenic wrote:

Try the following array formula...

=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B2:F2)),ROW(
INDIRECT("1:"&COUNTA(B6:D6)))),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

  #7   Report Post  
Steve Rolls
 
Posts: n/a
Default

Excellent ! Thank You all.

--
Steve
"Domenic" wrote in message
...
Try the following array formula...

=MIN(VLOOKUP(A6,A2:F4,SMALL(IF(B1:F1=TRANSPOSE(B6: D6),COLUMN(B2:F2)),ROW(
INDIRECT("1:"&COUNTA(B6:D6)))),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Steve Rolls" wrote:

Thanks to both of you. Both or your solutions worked. Thank You

I now have a followup question Is there a way for the fomula to
work if the criteria row only has one, two values (in b6:d6) ?
(of course there needs to be criteria in A6)
With the formula Max gave, if 3 criteria are not entered I get
#N/A.

Steve


--
Steve
"Alan Beban" wrote in message
...
Another try with the same layout:

=MIN(VLOOKUP(A6,A2:F4,MATCH(B6:D6,A1:F1,0),0)) array entered

Alan Beban

Max wrote:
One try ..

Assuming this table is in A1:F4

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5


and this "criteria range" is in A6:D6

PET Red Blue Yellow


Put in say, E6:

=MIN(TRANSPOSE(OFFSET($A$1,MATCH(A6,A:A,0)-1,MATCH(B6:D6,B1:F1,0))))

Array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Steve R" wrote in message
...

I am looking for a formula to return the minimum value from a table

(below)

Red Green Blue Yellow Orange
PET 6 8 14 3 2
OAK 5 6 7 8 12
SJC 2 7 11 8 5

If the following lookup info is entered into 4 cells(below)

PET Red Blue Yellow - The value "3" is returned since it is the
minimum
value in the "PET row" of the 3 items listed

Thanks for any help
Steve




  #8   Report Post  
Max
 
Posts: n/a
Default

"Steve Rolls" wrote
Excellent ! Thank You all.


You're welcome, Steve !

Liked the neater solution posted by Alan B,
and Domenic's "flexible" solution
when you threw in the additional condition (urgh! <g)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I lookup and return different values when the lookup value. kg Excel Discussion (Misc queries) 1 January 20th 05 01:53 AM
Lookup values in a list and return multiple rows of data Amanda L Excel Worksheet Functions 2 December 2nd 04 05:48 PM
Excel Lookup Functions Paul Adams Excel Worksheet Functions 1 November 10th 04 03:40 PM
Another Lookup function, please Butch Excel Worksheet Functions 3 November 3rd 04 09:52 AM
double lookup, nest, or macro? Josef.angel Excel Worksheet Functions 1 October 29th 04 09:50 AM


All times are GMT +1. The time now is 08:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"