i.e. I am looking up 80% and Doug and Justin share that score, so for the
the two scores of 80% it is returning Doug twice instead of Doug once and
Justin once. How do I get it to return one and then once that name is used
than return the other one when that score appears again?
Sorry for the question not being articulated very well. Thanks!
One way of approaching this is to detect if there are duplicates (i.e.
IF(A2=A1, ...) assuming column A contains your percentages, and in the
THEN part of the IF adjust the range of the table that the VLOOKUP
function is using, and in the ELSE part you have your normal VLOOKUP
function. Adjusting the table range is done by means of INDIRECT, with
a MATCH function which determines which row of the table the previous
name occurs on. I'm sure you can see that the formula needs to be
fitted to suit the actual data layout that you are using.
Hope this helps.
Pete
On Oct 24, 11:55 pm, Zombie0635 <Zombie0...@discussions.microsoft.com>
wrote:
E.g. =Vlookup(A1,$K1:$N1,2,0)
Just try editing your existing formula by ending it with ,0)
Just a guess I am taking as you have not posted the actual formula
Pete
On Oct 25, 7:49 am, claude jerry
> > Sorry for the question not being articulated very well. Thanks!- Hide quoted text -
>
> - Show quoted text -
Note: Because I am not profficient with Excel I use multiple tables to break
up my data into smaller pieces that I can handle until I finally get to my
desired results. The above table is the table I am drawing from to get my
final results. Here are the formulas I use on my final table: I use the
"large function" to bring over the winning % in a descending order, than I
use "vlookup's" to bring over the name, wins, and losses. Of course it
doesnt matter if people have the same win/loss record because numbers can be
identical, but it only returns one name for the same winning %. Here is an
example of my "vlookup" formula for my name:
=vlookup(l35,Sheet2!$G$8:$K$13,2,false).
I hope this info sheds a little more light on the subject manner, and thanks
for all of your help
In Sheet2 cells G8 to K13 you have a table of data like you have
posted. This was a bit confusing as it already seems to be sorted, but
I have assumed that is just coincidence, and sorted it by name in my
test.
In Sheet1 cells L35:L40 you have a formula like:
=LARGE(Sheet2!G$8:G$13,ROW(A1))
copied down, which gives you the percentages from the previous table
but in decreasing order. You can put this formula in M35 to get the
first name, as you have already indicated:
=VLOOKUP(L35,Sheet2!$G$8:$K$13,2,0)
In M36, however, you need a formula like this:
=IF(L36=L35,VLOOKUP(L36,INDIRECT("Sheet2!$G$"&8+MATCH(M35,Sheet2!H$8:H
$13,0)&":$K$13"),2,0),VLOOKUP(L36,Sheet2!$G$8:$K$13,2,0))
and then this formula should be copied down into M37:M40.
You can then use this formula in N35:
=VLOOKUP($M35,Sheet2!$H$8:$K$13,COLUMN(B1),0)
and this can be copied into O35 and P35, and then N35:P35 can be
copied down the next five rows.
This will give you your sorted table, and it will take account of
duplicate percentages - your names should be unique, though.
Hope this helps.
Pete
On Oct 25, 7:22 pm, Zombie0635 <Zombie0...@discussions.microsoft.com>
wrote:
> > > - Show quoted text -- Hide quoted text -
As I pointed out earlier, it basically adjusts the range of the table
being looked at if the "score" is the same as the previous one -
sorting by formula.
Pete
On Oct 26, 5:42 pm, Zombie0635 <Zombie0...@discussions.microsoft.com>
wrote: