Is there a way to get these functions to work in both directions, meaning
both left to right and right to left for VLOOKUP for instance?

Example:
I have these three columns:

Rank Name Sales
2 Jones 15
1 Smith 20
3 Brown 10

If I want to create a view in another part of the spreadsheet showing the
Name and Sales of the top ranked person, I can use the normal VLOOKUP
function such as VLOOKUP(1,$A$2:$C$4,2,FALSE) etc. However, if I also want
to be able to show the rank and name of anyone selling more than 12, I can't
just use the VLOOKUP function without also repeating the two columns to the
right of the Sales column.

Or is there a way around that?

VLOOKUP? It's a wrong choice.
Use Auto Filter, Advanced Filter, or Pivot table


"thexdane" wrote:

Is there a way to get these functions to work in both directions, meaning
both left to right and right to left for VLOOKUP for instance?

Example:
I have these three columns:

Rank Name Sales
2 Jones 15
1 Smith 20
3 Brown 10

If I want to create a view in another part of the spreadsheet showing the
Name and Sales of the top ranked person, I can use the normal VLOOKUP
function such as VLOOKUP(1,$A$2:$C$4,2,FALSE) etc. However, if I also want
to be able to show the rank and name of anyone selling more than 12, I can't
just use the VLOOKUP function without also repeating the two columns to the
right of the Sales column.

Or is there a way around that?

Assume the source data as posted in cols A to C, data from row2 down

Assume the cut-off sales will be input in D2, ie in D2: 12
Put in E2:
=IF($D$2="","",IF(C2$D$2,ROW(),""))
Leave E1 blank

Put in F2:
=IF(ROWS($1:1)COUNT($E:$E),"",INDEX(A:A,SMALL($E: $E,ROWS($1:1))))
Copy F2 to G2. Select E2:G2, copy down to cover the max expected extent of
source data. Hide away col E. Cols F and G returns the required ranks and
names for sales exceeding the fig input in D2, all neatly bunched at the top.
The rank/name extracts will be returned in the same order that they appear
within the source.

And if you would like it extracted in *descending* order by sales,
you could try this in another 3 adjacent empty cols to the right ..

Cut-off sales will be input in D2, ie in D2: 12 (as before)

In I2:
=IF($D$2="","",IF(C2$D$2,C2-ROW()/10^10,""))
Leave I1 blank

In J2:
=IF(ROWS($1:1)COUNT($E:$E),"",INDEX(A:A,MATCH(LAR GE($E:$E,ROWS($1:1)),$E:$E,0)))
Copy J2 to K2. Select I2:K2, copy down to cover the max expected extent of
source data. Hide away col I. Cols J and K returns the required ranks and
names for sales exceeding the fig input in D2, sorted in descending order by
sales, with all lines neatly bunched at the top.

If you want the sales figs to be returned as well, just copy J2 across by 1
more col to L2. Then select I2:L2 and fill down as before. Similarly for the
1st scenario.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thexdane" wrote:
Is there a way to get these functions to work in both directions, meaning
both left to right and right to left for VLOOKUP for instance?

Example:
I have these three columns:

Rank Name Sales
2 Jones 15
1 Smith 20
3 Brown 10

If I want to create a view in another part of the spreadsheet showing the
Name and Sales of the top ranked person, I can use the normal VLOOKUP
function such as VLOOKUP(1,$A$2:$C$4,2,FALSE) etc. However, if I also want
to be able to show the rank and name of anyone selling more than 12, I can't
just use the VLOOKUP function without also repeating the two columns to the
right of the Sales column.

Or is there a way around that?

Errata
In J2:
=IF(ROWS($1:1)COUNT($E:$E),"",INDEX(A:A,MATCH(LAR GE($E:$E,ROWS($1:1)),$E:$E,0)))

In J2 should be:
=IF(ROWS($1:1)COUNT($I:$I),"",INDEX(A:A,MATCH(LAR GE($I:$I,ROWS($1:1)),$I:$I,0)))

(it should point to the criteria col I)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks MAX and Mama.

"Max" wrote:

Errata
In J2:
=IF(ROWS($1:1)COUNT($E:$E),"",INDEX(A:A,MATCH(LAR GE($E:$E,ROWS($1:1)),$E:$E,0)))

In J2 should be:
=IF(ROWS($1:1)COUNT($I:$I),"",INDEX(A:A,MATCH(LAR GE($I:$I,ROWS($1:1)),$I:$I,0)))

(it should point to the criteria col I)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

welcome, hope it worked for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"thexdane" wrote in message
...
Thanks MAX and Mama.