Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP formula not working right
I'm trying to identify the person with the lowest score in the column. I'm using the formula =IF(J16="1",LOOKUP((MIN(J4:J15)),J4:J15,$D$4:$D$15 ),"") Column D represents the names Column J represents their score cell J16 indicates there was 1 low score Sometimes I get the right name and sometimes I get #N/A. -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=505478 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP formula not working right
Why the quotes around the digit 1? Surely the 1 in J16 is numeric not text
I tried =IF(J16=1,LOOKUP((MIN(J4:J15)),J4:J15,$D$4:$D$15), "") with some dummy data and it worked. Also try =INDEX($D$4:$D$15,MATCH(MIN(J4:J15),J4:J15,0)) OR =IF(J16=1, ($D$4:$D$15,MATCH(MIN(J4:J15),J4:J15,0)) ,"") -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "pdgaustintexas" <pdgaustintexas.229d2t_1138306253.4849@excelforu m-nospam.com wrote in message news:pdgaustintexas.229d2t_1138306253.4849@excelfo rum-nospam.com... I'm trying to identify the person with the lowest score in the column. I'm using the formula =IF(J16="1",LOOKUP((MIN(J4:J15)),J4:J15,$D$4:$D$15 ),"") Column D represents the names Column J represents their score cell J16 indicates there was 1 low score Sometimes I get the right name and sometimes I get #N/A. -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=505478 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP formula not working right
Sorry, none of those formulas worked just right. The reason for the "1" is that cell J16 has a formula to find the _only_ lowest number =IF(FREQUENCY(J4:J15,(MIN(J4:J15)))=1, "1","") Basically what this is is a golf tournament scorecard. I have a list of names per flight. I want to find the lowest score per hole and the player who shot it. 2 or more low scores, the cell is left blank. The formula I'm using only works on a few names, otherwise I get #N/A. -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=505478 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP formula not working right
That really is not the way FREQUENCY is used. Try
=IF(COUNTIF(J4:J15,MIN(J4:J15))=1,1,"") I do not understand why you have put the 1 in quotes when it is numeric. In my answer I mistyped the second formula; it should be =IF(J16=1,INDEX($D$4:$D$15,MATCH(MIN(J4:J15),J4:J1 5,0)),"") If you really want text in J16 use =IF(COUNTIF(J4:J15,MIN(J4:J15))=1,"1","") and =IF(J16="1",INDEX($D$4:$D$15,MATCH(MIN(J4:J15),J4: J15,0)),"") Both version work for me. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "pdgaustintexas" wrote in message news:pdgaustintexas.229fe2_1138309207.653@excelfor um-nospam.com... Sorry, none of those formulas worked just right. The reason for the "1" is that cell J16 has a formula to find the _only_ lowest number =IF(FREQUENCY(J4:J15,(MIN(J4:J15)))=1, "1","") Basically what this is is a golf tournament scorecard. I have a list of names per flight. I want to find the lowest score per hole and the player who shot it. 2 or more low scores, the cell is left blank. The formula I'm using only works on a few names, otherwise I get #N/A. -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=505478 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP formula not working right
IT WORKS!! Thanks alot! :) -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=505478 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP formula not working right
IT WORKS!! Thanks alot! :) -- pdgaustintexas ------------------------------------------------------------------------ pdgaustintexas's Profile: http://www.excelforum.com/member.php...o&userid=30600 View this thread: http://www.excelforum.com/showthread...hreadid=505478 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum formula not working, producing incorrect answers Excel 2003 | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
Array formula not working | Excel Worksheet Functions |