Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up unique value over several columns of data
Hi,
Can anyone think of a way to lookup a value in an table that covers several columns of unique discreet values and then returns the row number in which that value occurs regardless of the column number? E.g. B2:D4 contains unique discreet values and one of these value is 3, which is in cell C4. The lookup formula searches for 3 in columns B:D and returns 4 for the row number. MATCH seems to only handle one column at a time. I realize that I could write this functionality with a nested IF statement, but imagine that the table covers many columns, in which case the IF statement method would be impractical. Hope this makes sense. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up unique value over several columns of data
Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER... =MIN(IF($B$2:$D$4=3,ROW($B$2:$D$4))) If you'd like to return NA (Not Available) when the number doesn't exist, try the following formula instead... =IF(COUNTIF($B$2:$D$4,3),MIN(IF($B$2:$D$4=3,ROW($B $2:$D$4))),"NA") Hope this helps! In article , H wrote: Hi, Can anyone think of a way to lookup a value in an table that covers several columns of unique discreet values and then returns the row number in which that value occurs regardless of the column number? E.g. B2:D4 contains unique discreet values and one of these value is 3, which is in cell C4. The lookup formula searches for 3 in columns B:D and returns 4 for the row number. MATCH seems to only handle one column at a time. I realize that I could write this functionality with a nested IF statement, but imagine that the table covers many columns, in which case the IF statement method would be impractical. Hope this makes sense. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up unique value over several columns of data
Hi,
If I understand your question: =MAX((B2:D4=B8)*ROW(B2:D4)) Where the number you want to find is in cell B8. This is an array formula so press Shift Ctrl Enter to enter in not Enter. Since they are unique numbers in the range, you could use MIN, SUM, COUNT, AVERAGe instead of MAX. You could also use SUMPRODUCT and then you wouldn't neet to do an array entry. =SUMPRODUCT((B2:D4=B8)*ROW(B2:D4)) -- Cheers, Shane Devenshire "Domenic" wrote: Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =MIN(IF($B$2:$D$4=3,ROW($B$2:$D$4))) If you'd like to return NA (Not Available) when the number doesn't exist, try the following formula instead... =IF(COUNTIF($B$2:$D$4,3),MIN(IF($B$2:$D$4=3,ROW($B $2:$D$4))),"NA") Hope this helps! In article , H wrote: Hi, Can anyone think of a way to lookup a value in an table that covers several columns of unique discreet values and then returns the row number in which that value occurs regardless of the column number? E.g. B2:D4 contains unique discreet values and one of these value is 3, which is in cell C4. The lookup formula searches for 3 in columns B:D and returns 4 for the row number. MATCH seems to only handle one column at a time. I realize that I could write this functionality with a nested IF statement, but imagine that the table covers many columns, in which case the IF statement method would be impractical. Hope this makes sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding unique entries among two columns of alphanumeric data | Excel Worksheet Functions | |||
summing columns if there is data | Excel Worksheet Functions | |||
How to Copy and Paste Several Columns of Data into Excel... ? | Excel Discussion (Misc queries) | |||
Compare multiple column of data and list out common and unique component in adj columns | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions |