lookups

[Cossie]

I am attempting to lookup and return a value with the formula below, but all that it will return is the value of AI (ipswich). The shhet looks like this.

a b c d
Ipswich 12.00 13.00 Above Max
Beenleigh 22.00 19.00 Above Max

In cell f8:f20 i have the following formula, wih the value of f7 = "above max".
=INDEX($A$3:$A$20,MATCH($F$7,$d$3:$d$20,FALSE),1)
and in all the cells f8:f20 it returns Ipswich.

What am i doing wrong please.

[Yogi Anand]

On 2002-05-14 21:41, Cossie wrote:
I am attempting to lookup and return a value with the formula below, but all that it will return is the value of AI (ipswich). The shhet looks like this.

a b c d
Ipswich 12.00 13.00 Above Max
Beenleigh 22.00 19.00 Above Max

In cell f8:f20 i have the following formula, wih the value of f7 = "above max".
=INDEX($A$3:$A$20,MATCH($F$7,$d$3:$d$20,FALSE),1)
and in all the cells f8:f20 it returns Ipswich.

What am i doing wrong please.

Your formula is correctly producing one (the same) result in F8:F20

Your formula is asking for value in column 1 of range A3:A20, from row number in range D3:D20 that matches the value in cell F7 -- and that is row number 1 of range D3:D20.

You need to revisit your formula with reference to what you are trying to accomplish.

Regards!

[Cossie]

the result that i want is for F9 to say beenleigh as it meets the criteria of "above max" and in F10 i need it to place the next location that has a value of "above max"

eg
F8 = above max

F9 = Ipswich (as a result of formula)
F10 = beenleigh (as a result of formula)
F11 = townsville (as a result of formula)
F12

[Yogi Anand]

On 2002-05-14 22:11, Cossie wrote:
the result that i want is for F9 to say beenleigh as it meets the criteria of "above max" and in F10 i need it to place the next location that has a value of "above max"

eg
F8 = above max

F9 = Ipswich (as a result of formula)
F10 = beenleigh (as a result of formula)
F11 = townsville (as a result of formula)
F12

If this is what you want, change the formula to ...

=INDEX($A3:$A$20,MATCH($F$7,$D$3:$D$20,FALSE),1)

I don't quite know the contex in which the formula is being used -- so I can't vouch on the logistics being used in your formulation; however please notice that in the formula, the row number in the lookup reference is now relative.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

[Cossie]

Yogi Thanks for your patience with this. I will try and explain myself more fully (i hope).

My sheet contains a list of places and their relative performance and this performance is rated as above average, average, below average and below minimum.

My sheest llok like this:

Office Last week this week Performance
Ipswich 12.0 13.0 Above Average
Wagga 15.0 16.0 Above Average
Auburn 10.2 9.5 Below Average

In a separate part of the sheet i am trying to create a formula that will list all the above average offices in one column, all the average offices in another column, all the below average offices in another column etc.

My formula
=INDEX($A$3:$A$27,MATCH($F$7,$E$3:$E$27,FALSE),1) returns the correct value in the first cell, but in the next cell it returns the same value as in the first cell.

I want it to return Ipswich and then Wagga, but it returns Ipswich and Ipswich.

Maybe I am using the wrong function.

Hope this explains it more clearly. Thanks for you help.

[Aladin Akyurek]

On 2002-05-15 17:38, Cossie wrote:
Yogi Thanks for your patience with this. I will try and explain myself more fully (i hope).

My sheet contains a list of places and their relative performance and this performance is rated as above average, average, below average and below minimum.

My sheest llok like this:

Office Last week this week Performance
Ipswich 12.0 13.0 Above Average
Wagga 15.0 16.0 Above Average
Auburn 10.2 9.5 Below Average

In a separate part of the sheet i am trying to create a formula that will list all the above average offices in one column, all the average offices in another column, all the below average offices in another column etc.

My formula
=INDEX($A$3:$A$27,MATCH($F$7,$E$3:$E$27,FALSE),1) returns the correct value in the first cell, but in the next cell it returns the same value as in the first cell.

I want it to return Ipswich and then Wagga, but it returns Ipswich and Ipswich.

Maybe I am using the wrong function.

Hope this explains it more clearly. Thanks for you help.

Lets say that A1:D4 houses your sample data:

{"Office","Last week","this week","Performance";
"Ipswich",12,13,"Above Average";
"Wagga",15,16,"Above Average";
"Auburn",10.2,9.5,"Below Average"}

Given a lookup value you are interested in retrieving multiple offices (if any) from A.

In E1 enter: =MATCH(9.99999999999999E+307,Sheet1!B:B) [ This computes how many rows of data you have. ]

In E2 enter: =ROW(2:2) [This keeps track of the row where your real data start. ]

In F1:H1 enter: Above Average, Average, and Below Average, respectively [ These are lookup values for which you want to fetch the associated offices. ]

In F2 enter: =IF(COUNTIF($D:$D,F$1),INDEX($A:$A,MATCH(F$1,$D:$D,0)),"")

Copy this across to F2. Notice that this formula fetches the first names that are associated with Above Average, Average, and Below Average.

In F3 enter: =IF(COUNTIF($D:$D,F$1)>ROW()-$E$2,INDEX($A:$A,MATCH(F$1,OFFSET($D$1,MATCH(F2,$A:$A,0),0,$E$1,1),0)+MATCH(F2,$A:$A,0)),"")

Copy this formula across to F3 then down until no more names are retrieved.

Notice that the latter formula retrieves the 2nd, the 3rd, etc. names that are associated with a lookup value of interest.

Note. I adapted the foregoing from a post of mine at:

http://www.mrexcel.com/wwwboard/messages/21482.html

The below figure shows how all this looks:

Microsoft Excel - aaMultValRetrieval Cossie.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1		=	Office
*	A	B	C	D	E	F	G	H
1	Office	Last week	this week	Performance	:alert('=MATCH(9.99999999999999E+307,Sheet1!B:B)')>4	Above Average	Average	Below Average
2	Ipswich	12	13	Above Average	:alert('=ROW(2:2)')>2	:alert('=IF(COUNTIF($D:$D,F$1),INDEX($A:$A,MATCH(F$1,$D:$D,0)),"")')>Ipswich	:alert('=IF(COUNTIF($D:$D,G$1),INDEX($A:$A,MATCH(G$1,$D:$D,0)),"")')>*	:alert('=IF(COUNTIF($D:$D,H$1),INDEX($A:$A,MATCH(H$1,$D:$D,0)),"")')>Auburn
3	Wagga	15	16	Above Average	*	:alert('=IF(COUNTIF($D:$D,F$1)%3EROW()-$E$2,INDEX($A:$A,MATCH(F$1,OFFSET($D$1,MATCH(F2,$A:$A,0),0,$E$1,1),0)+MATCH(F2,$A:$A,0)),"")')>Wagga	:alert('=IF(COUNTIF($D:$D,G$1)%3EROW()-$E$2,INDEX($A:$A,MATCH(G$1,OFFSET($D$1,MATCH(G2,$A:$A,0),0,$E$1,1),0)+MATCH(G2,$A:$A,0)),"")')>*	:alert('=IF(COUNTIF($D:$D,H$1)%3EROW()-$E$2,INDEX($A:$A,MATCH(H$1,OFFSET($D$1,MATCH(H2,$A:$A,0),0,$E$1,1),0)+MATCH(H2,$A:$A,0)),"")')>*
4	Auburn	10.2	9.5	Below Average	*	:alert('=IF(COUNTIF($D:$D,F$1)%3EROW()-$E$2,INDEX($A:$A,MATCH(F$1,OFFSET($D$1,MATCH(F3,$A:$A,0),0,$E$1,1),0)+MATCH(F3,$A:$A,0)),"")')>*	:alert('=IF(COUNTIF($D:$D,G$1)%3EROW()-$E$2,INDEX($A:$A,MATCH(G$1,OFFSET($D$1,MATCH(G3,$A:$A,0),0,$E$1,1),0)+MATCH(G3,$A:$A,0)),"")')>*	:alert('=IF(COUNTIF($D:$D,H$1)%3EROW()-$E$2,INDEX($A:$A,MATCH(H$1,OFFSET($D$1,MATCH(H3,$A:$A,0),0,$E$1,1),0)+MATCH(H3,$A:$A,0)),"")')>*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Aladin

[Cossie]

BRILLIANT result exactly what i was looking for.

Thanks everybody for your assistance