i have 2 workbooks
workbook 1: KEJOHANAN OLAHRAGA MSSM 2012 SM
workbook 2: Keputusan_0
my question:
in workbook 1, cell N10,
a) search and match L18TKB (cell N8) and 100M (M10) in workbook 2 from E6 to F405, if match
b) search athlete start from number F, if can find, return the athlete number example F50 ( yellow row) into workbook 1 (cell N10) (green cell)
c) if cannot find athlete start from number F, return "-"
Last edited by cboys00; 10-31-2012 at 08:50 AM.
hopefully some expert can help...thanks
this is the last formula in my program.....
if cannot solveall program will gone
any response from expert...please
Hi cboys00,
First, insert a column to the left of KATEGORI in the keputusan_0.xlsx and type this formula in cell E6: =F6&G6
Copy this formula all the way down. You can hide this column afterwards.
Then try this formula in N10... =VLOOKUP(N$8&$M10,'[keputusan_0.xlsx]KEPUTUSAN'!$E$6:$H$405,4,0)
Copy this formuall all the way down and where ever you need it.
Does this get what you need? This is a little trick that I use whenever I have to lookup 2 values. I first have to concatenate them.
Hope this helps,
Dennis
it works..thanksOriginally Posted by djapigo
but need another function, see first post, question c
c) if cannot find athlete start from number F, return "-" is mean no athlete
thanks
Oops, sorry about that... try this...
=IFERROR(VLOOKUP(N$8&$M10,'[keputusan_0.xlsx]KEPUTUSAN'!$E$6:$H$405,4,0),"-")
cannot work....this formula still have same output will =VLOOKUP(N$8&$M10,'[keputusan_0.xlsx]KEPUTUSAN'!$E$6:$H$405,4,0)
why? where r mistake
i try this formula:
I did some modification on the cells in keputusan_0 (workbook 2). add new colum E4, F4, G4
in workbook 1 (cell N10), put in this formula:
=IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B10&"AKHIR",keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,8,0),""),1),0)=1,"E",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B10&"AKHIR",keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,10,0),""),1),0)=1,"P",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B10&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,12,0),""),1),0)=1,"G",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B10&"AKHIR",keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,14,0),""),1),0)=1,"K4",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B10&"AKHIR",keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,10,0),""),1),0)=1,"K5","-"))))
but i know it too long and got wrong
a) TOO MANY NESTED THAN ALLOWED IN THE CURRENT FORMAT
b) in last formula (K5) * red color * cannot appear in workbook 1 (cell N10)
any one can help to shorten this formula?
Last edited by cboys00; 11-02-2012 at 09:57 AM.
Hello,
I can't seem to follow your logic in your formula. There seems to be several items wrong with it.
1. You only have a limited number of IF statements (which includes IFERROR)
2. You are missing some front brackets "[" on some of the ranges
3. SEARCH only has 2 variable, not 3
4. I can't follow ,1),0)=1,"E" ???
5. I think your last VLOOKUP should be column 16, not 10 again
Anyway, is this what you have in mind...
If the first letter in the VLOOKUP equals F in column H, then give me "E", else
If the first letter in the VLOOKUP equals F in column J, then give me "P", else
If the first letter in the VLOOKUP equals F in column L, then give me "G", else
If the first letter in the VLOOKUP equals F in column N, then give me "K4", else
If the first letter in the VLOOKUP equals F in column P, then give me "K5"
If so, try this formula,
=IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet1)))>0,"E",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet2)))>0,"P",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet3)))>0,"G",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet4)))>0,"K4",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet5)))>0,"K5","-")))))
Notice that I named some of the ranges in keputusan_0.xlsx… make sure they are the same lengths!
cells E6:E405 = kategori
cells F6:F405 = acara
cells G6:G405 = peringkat
cells H6:H405 = atlet1
cells J6:J405 = atlet2
cells L6:L405 = atlet3
cells N6:N405 = atlet4
cells P6:P405 = atlet5
This will mean that we don’t need the helper column where we concatenated the 3 cells.
Anyway, try this and see if this is what you want.
By the way, what do you want to see if there are 2 records in keputusan_0.xlsx with "F" on the same row? For example, see row 100. What do you want to see the formula do in C5? Both? The first entry or the second entry?
Let me know,
Dennis
Last edited by djapigo; 11-02-2012 at 02:18 PM.
i try your formula, but it display #NAME? in C10 (workbook 1)
Did you name the ranges properly?
By the way, is my assumption of what you want correct?
Let me know if you can't follow the formula, it's a big mess.
Last edited by djapigo; 11-02-2012 at 08:35 PM.
You are right. This is what I want.
Is that possible to display the first found value in the 1st row, then 2nd found value in the 2nd row? I will insert another row for each "acara".
Thank you.
That would actually make it very hard... can we show both in the same cell...
If so, change the formula into separate IF statements, for example...
=IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet1)))>0,"E ","")&IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet2)))>0,"P ","")&IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet3)))>0,"G ","")&IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet4)))>0,"K4 ","")&IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet5)))>0,"K5","")
Last edited by djapigo; 11-02-2012 at 09:17 PM.
Cannot change to seperate cell oh
why this formula return #NAME in cell C10 (workbook 1),
can not be corrected?
this formula can return what i need, but can i add one more IF statement (last statement) (my project was successes)
for search "k5"
=IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,8,0),""),1),0)=1,"E",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,10,0),""),1),0)=1,"P",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,12,0),""),1),0)=1,"G",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,14,0),""),1),0)=1,"K4","-"))))
I'm not sure what you to do... let me know what you want to see...
for easy way....
this formula can return what i need, but can i add one more IF statement (last statement) (my project was successes)
for search "k5" in workbook 2
=IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,8,0),""),1),0)=1,"E",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,10,0),""),1),0)=1,"P",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,12,0),""),1),0)=1,"G",IF(IFERROR(SEARCH("F",IFERROR(VLOOKUP(C$8&$B14&"AKHIR",[keputusan_0.xlsx]KEPUTUSAN!$A$6:$Q$1000,14,0),""),1),0)=1,"K4","-"))))
Hi cboys00,
You have too many nested IFs and that's why this formula was not working... how about VBA? We can create a formula that will do the same...
Let me know what you think.
Why do you want to go back to this formula? Wasn't the other one working?
Last edited by djapigo; 11-03-2012 at 04:03 AM.
this is a last formula.....any idea to shorten this formula.....i am not familiar with VBA
i have try another formula (your suggestion) ..... but it return #NAME?
can solve it
=IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet1)))>0,"E",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet2)))>0,"P",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet3)))>0,"G",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet4)))>0,"K4",IF(SUMPRODUCT(--(keputusan_0.xlsx!kategori=C$8),--(keputusan_0.xlsx!acara=$B10),--(keputusan_0.xlsx!peringkat="AKHIR"),--ISNUMBER(SEARCH("F",keputusan_0.xlsx!atlet5)))>0,"K5","-")))))
OK, I think we got it now... I removed the inner IFERRORs inside the SEARCH...
Try this...
=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","-")))))
Remember, you have to name the ranges... once you do that, the formula should work...
Anyway, I think the new formula will work too...
wah...very good.....it solved
very thanks djapigo
I'm glad it finally worked!
thanks djapigo....you help a lot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks