Variable Array in VLookup

[toge89]

Hi,

First time here so please forgive any faux pas or poor description

Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:

=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))

Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.

I'm not very familiar with indirect, so I think the issues is probably there.

Thanks for any help anyone can provide.

[Jonmo1]

it checks for an error in the Vlookup, if its not an error then it does the VLookup.

Actually, I think you have it backwards.

=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))

If it's an error, it does the SAME vlookup (the one that was an error) again.
If it's NOT an error, then it does the different vlookup.

[Jonmo1]

Try

=IFERROR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),VLOOKUP($J16,Men!$A$1:$G$43,7,FALSE))

[toge89]

Jonmo,
Thought that too but the iserror function returns a false if it is an error so it wouldn't do the same search. Also thought about IFERROR but it doesn't allow for an 'else' (as far as I know) so it doesn't really help either. The tricky part is I don't want to hide the error, I want it to keep looking.

So how the ranking works is if two people were tied for 3rd it would be 1,2,3,3,5,6,etc... so I want the function to recognize 4 isn't there and go back to search for 3 but starting where the last one left off so it won't be a duplicate result.

Searching from the bottom up would work out too but not sure how that works either.

Thanks for the help and quick feedback.

[Jonmo1]

I think you missunderstand what IFERROR does.

=IFERROR(formula1,formula2)

If formula1 is an error, it returns the value of formula2
If formula1 is NOT an error, it returns the value of formula1

So with this
=IFERROR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),VLOOKUP($J16,Men!$A$1:$G$43,7,FALSE))

If VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE) is an an error, it returns the value of VLOOKUP($J16,Men!$A$1:$G$43,7,FALSE)
If it is NOT an error, then it returns the value of VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)

[toge89]

Oh. Yes that does simplify the formula considerably then, but that returns the same result as the previous cell?

example:
13 Dan Bailey
14 Brandon Swan
15 #N/A
16 Kyle Kasperbauer

So this is a small view but the number on the left is fixed and it is what the VLookup is using (J16 = 14, J17 = 15, etc). So there is a tie for 14 so rather than show the other person tied, it is showing #N/A. So I want to make the Table_array in the second Vlookup to be variable, based on where the previous VLookup finished (Finding 14 Brandon Swan) so it shows the other person in with 14 in the Table_array.

The error portion wasn't nearly as clean as yours, but it isn't the portion I'm having trouble with.

Really appreciate the clarification on the iferror formula though.

[Jonmo1]

Can you post a sample workbook?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[toge89]

Yep.

Let me know if this helps.

Thanks for all the input.

[Jonmo1]

Put in a helper column on the Men sheet.
Insert a new column Between cols A and B

Then in B1 and filled down, put
=RANK(A1,$A$1:$A$43,1)+COUNTIF(A$1:A1,A1)-1

Then in the calculation sheet, K3 and filled down, put
=VLOOKUP($J3,Men!$B$1:$H$43,7,FALSE)

[toge89]

That works GREAT, thanks Jonmo.

[Jonmo1]

You're welcome.