how to return 2 winner

this formula is correct...no problem.

=IF(IFERROR(SEARCH("F",VLOOKUP(C$8&$B10&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,8,0)),0)=1,"E",IF(IFERROR(SEARCH("F",VLOOKUP(C$8&$B10&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,10,0)),0)=1,"P",IF(IFERROR(SEARCH("F",VLOOKUP(C$8&$B10&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,12,0)),0)=1,"G",IF(IFERROR(SEARCH("F",VLOOKUP(C$8&$B10&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,14,0)),0)=1,"K4",IF(IFERROR(SEARCH("F",VLOOKUP(C$8&$B10&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,16,0)),0)=1,"K5","-")))))


the problem here

the formula only find and return 1 winner start from "F"

maybe in one event have 2 winner from same school, example

first winner "F100" (H10, workbook 2) return to cell C20, workbook 1 (this is ok)

second winner "F05" (N10, workbook 2) return to cell C21, workbook 1 ( how to return this value in C21)

** i had drag the formula into C21, it return "-"

so how

for your info:

workbook 1: KEJOHANAN OLAHRAGA MSSM 2012 SM

workbook 2: keputusan_0

Hi cboys00,

It's me again and I think I have a solution for you... I'm assuming there can only be 2 possible winners from the same school.

The current formula gets us the first winner, but it's not made to get the 2nd winner.

Why don't you "reverse" the formula... in other words, instead of looking at column 8 to get E, column 10 to get P, 12 to get G, 14 to get K4, and 16 to get K5... change the formula to first look at column 16 to get K5, 14 to get K4, 12 to get G, etc.

This not the ideal format for any spreadsheet, but this new formula can only be for the duplicates.

Also note that this new formula has to refer to the correct B column... currently, your formula is looking at B21 which is blank, either copy the acara value from above or change your formula to look at B20...

Let me know if this works...

Dennis

Originally Posted by djapigo

Hi cboys00,

Why don't we put these 2 big formulas off to the side in different columns (for example column X) which can be hidden, then have a simple formula in the main section that simply says something like...

C21: =IF(X3=X2,"-",X3)

Does that makes sense?

Let me know if you need more clarification. Sometimes if the formula works, just be creative on how to present it... if you are not limited on the columns, then take advantage of them and use extra columns... sure it's not as clean as a standalone formula, but the program has its limitations too... so work around it and find other ways to solve your problem...

Take care,
Dennis

Ok, can do.

A little trouble in editting, but in acceptable level.

Step
1. Add another row under each acara.
2. Insert that "reserve" formula.
3. In the next row, use the current IF logic.
4. Drag row by row.
5. Drag down the mark by column.
6. Remove the mark for second column.
7. Hide the second column.
8. Recolouring kategori "P"

thanks djapigo

And now repeat to the other columns...

Glad it worked!

Originally Posted by djapigo

And now repeat to the other columns...

Glad it worked!

very thanks djapigo