Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use HLOOKUP or other command to find a value within a range
Simple question...I am looking for a value within a range of nine cells in a
row. If the value is not present I would like for the value that is not present (false) to be displayed. I tried a nested if statement, but the will only work on 7 conditions. For example: Take your average Suduko game. In Row1, numbers 1-9 can appear in a random order. If I want to know if the number 1 has been entered in any of the nine row cells, logically I would say "If the contents within B2:B10<1, display 1" and repeat in the next cell, "If the contents within B2:B10<2,display 2" and so on...up to the number 9. This way the person doing the puzzle will know what entries are left. B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING VALUE Row1 6 7 9 2 1 5 =formula to check for value If not present, value is displays |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use HLOOKUP or other command to find a value within a range
Corrected Example...
B2 B3 B4 B5 B6 B7 B8 B9 B10 B15 MISSING VALUE Row1 6 7 9 2 1 5 =formula to check for value If not present, value displays |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use HLOOKUP or other command to find a value within a range
What do you want to return if the value is present? Nothing?
=IF(COUNTIF($B$2:$B$10,ROWS($B$1:B1))=0,ROWS($B$1: B1),"") copy down btw one of the MS Excel developers (Dan Cory) has constructed a Sudoko solver using built in functions http://office.microsoft.com/en-us/te...809721033.aspx another one using code is here (by MVP Andy Pope) http://andypope.info/fun/sudoku.htm -- Regards, Peo Sjoblom (No private emails please) "antwash" wrote in message ... Simple question...I am looking for a value within a range of nine cells in a row. If the value is not present I would like for the value that is not present (false) to be displayed. I tried a nested if statement, but the will only work on 7 conditions. For example: Take your average Suduko game. In Row1, numbers 1-9 can appear in a random order. If I want to know if the number 1 has been entered in any of the nine row cells, logically I would say "If the contents within B2:B10<1, display 1" and repeat in the next cell, "If the contents within B2:B10<2,display 2" and so on...up to the number 9. This way the person doing the puzzle will know what entries are left. B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING VALUE Row1 6 7 9 2 1 5 =formula to check for value If not present, value is displays |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use HLOOKUP or other command to find a value within a r
Thanks much Peo Sjoblom... That did the trick. The links were good too..
they helped me better understand the function.. "Peo Sjoblom" wrote: What do you want to return if the value is present? Nothing? =IF(COUNTIF($B$2:$B$10,ROWS($B$1:B1))=0,ROWS($B$1: B1),"") copy down btw one of the MS Excel developers (Dan Cory) has constructed a Sudoko solver using built in functions http://office.microsoft.com/en-us/te...809721033.aspx another one using code is here (by MVP Andy Pope) http://andypope.info/fun/sudoku.htm -- Regards, Peo Sjoblom (No private emails please) "antwash" wrote in message ... Simple question...I am looking for a value within a range of nine cells in a row. If the value is not present I would like for the value that is not present (false) to be displayed. I tried a nested if statement, but the will only work on 7 conditions. For example: Take your average Suduko game. In Row1, numbers 1-9 can appear in a random order. If I want to know if the number 1 has been entered in any of the nine row cells, logically I would say "If the contents within B2:B10<1, display 1" and repeat in the next cell, "If the contents within B2:B10<2,display 2" and so on...up to the number 9. This way the person doing the puzzle will know what entries are left. B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING VALUE Row1 6 7 9 2 1 5 =formula to check for value If not present, value is displays |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solution found based on your reply
Based on your formula... I was able to figure it out...
i.e. searching for the number 1 in affected row, showing that it was used and displaying the number if it is not used. =IF(COUNTIF(B2:J2,1),"Used","1") Thanks much for your assistance. This group rocks! "Peo Sjoblom" wrote: What do you want to return if the value is present? Nothing? =IF(COUNTIF($B$2:$B$10,ROWS($B$1:B1))=0,ROWS($B$1: B1),"") copy down btw one of the MS Excel developers (Dan Cory) has constructed a Sudoko solver using built in functions http://office.microsoft.com/en-us/te...809721033.aspx another one using code is here (by MVP Andy Pope) http://andypope.info/fun/sudoku.htm -- Regards, Peo Sjoblom (No private emails please) "antwash" wrote in message ... Simple question...I am looking for a value within a range of nine cells in a row. If the value is not present I would like for the value that is not present (false) to be displayed. I tried a nested if statement, but the will only work on 7 conditions. For example: Take your average Suduko game. In Row1, numbers 1-9 can appear in a random order. If I want to know if the number 1 has been entered in any of the nine row cells, logically I would say "If the contents within B2:B10<1, display 1" and repeat in the next cell, "If the contents within B2:B10<2,display 2" and so on...up to the number 9. This way the person doing the puzzle will know what entries are left. B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING VALUE Row1 6 7 9 2 1 5 =formula to check for value If not present, value is displays |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search range of cells, find a value, output adjoining cell. How? | Excel Worksheet Functions | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Help, how to find a range of data? | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) |