| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matrix Type Lookup
Hello all.... I need to return the row where two statements are
true.... I have two columns, both with data. Column a is the "Role", column B is the Application.... I need to to an index and match or something like that.... here's the issue... both columns can have multiple entries that are the same. Role Admin Asst Manager Desktop Admin Admin Asst Manager Desktop Admin etc. Application Office 2007 Office 2007 Office 2007 Internet Explorer Internet Explorer Internet Explorer I want to know the row where say Manager & Internet Explorer are the same. so no vlookup so categorize column a, find all the rows that = true, then do the same for b, and return the single row that has both as true. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matrix Type Lookup
So what result are you looking for? The actual row number?
-- Biff Microsoft Excel MVP "Search & You Will Find" wrote in message ... Hello all.... I need to return the row where two statements are true.... I have two columns, both with data. Column a is the "Role", column B is the Application.... I need to to an index and match or something like that.... here's the issue... both columns can have multiple entries that are the same. Role Admin Asst Manager Desktop Admin Admin Asst Manager Desktop Admin etc. Application Office 2007 Office 2007 Office 2007 Internet Explorer Internet Explorer Internet Explorer I want to know the row where say Manager & Internet Explorer are the same. so no vlookup so categorize column a, find all the rows that = true, then do the same for b, and return the single row that has both as true. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matrix Type Lookup
On Jan 21, 1:58*pm, "T. Valko" wrote:
So what result are you looking for? The actual row number? -- Biff Microsoft Excel MVP "Search & You Will Find" wrote in ... Hello all.... I need to return the row where two statements are true.... I have two columns, both with data. Column a is the "Role", column B is the Application.... I need to to an index and match or something like that.... here's the issue... both columns can have multiple entries that are the same. Role Admin Asst Manager Desktop Admin Admin Asst Manager Desktop Admin etc. Application Office 2007 Office 2007 Office 2007 Internet Explorer Internet Explorer Internet Explorer I want to know the row where say Manager & Internet Explorer are the same. so no vlookup so categorize column a, find all the rows that = true, then do the same for b, and return the single row that has both as true.- Hide quoted text - - Show quoted text - On a separate sheet I have a column of Roles in column A (beginning at A2) and a row of Applications beginning at B1. Where the two intersect, I want to put an "X". |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matrix Type Lookup
On Jan 21, 2:45*pm, "Search & You Will Find"
wrote: On Jan 21, 1:58*pm, "T. Valko" wrote: So what result are you looking for? The actual row number? -- Biff Microsoft Excel MVP "Search & You Will Find" wrote in ... Hello all.... I need to return the row where two statements are true.... I have two columns, both with data. Column a is the "Role", column B is the Application.... I need to to an index and match or something like that.... here's the issue... both columns can have multiple entries that are the same. Role Admin Asst Manager Desktop Admin Admin Asst Manager Desktop Admin etc. Application Office 2007 Office 2007 Office 2007 Internet Explorer Internet Explorer Internet Explorer I want to know the row where say Manager & Internet Explorer are the same. so no vlookup so categorize column a, find all the rows that = true, then do the same for b, and return the single row that has both as true.- Hide quoted text - - Show quoted text - On a separate sheet I have a column of Roles in column A (beginning at A2) and a row of Applications beginning at B1. Where the two intersect, I want to put an "X".- Hide quoted text - - Show quoted text - So I was thinking if i could get the row number, I could do a formula that says if the row number exists, then "X", othwerwise blank. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matrix Type Lookup
Try something like this array formula** :
=IF(SUM((Sheet1!$A$2:$A$7=$A2)*(Sheet1!$B$2:$B$7=B $1)),"X","") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copied across then down as needed. -- Biff Microsoft Excel MVP "Search & You Will Find" wrote in message ... On Jan 21, 2:45 pm, "Search & You Will Find" wrote: On Jan 21, 1:58 pm, "T. Valko" wrote: So what result are you looking for? The actual row number? -- Biff Microsoft Excel MVP "Search & You Will Find" wrote in ... Hello all.... I need to return the row where two statements are true.... I have two columns, both with data. Column a is the "Role", column B is the Application.... I need to to an index and match or something like that.... here's the issue... both columns can have multiple entries that are the same. Role Admin Asst Manager Desktop Admin Admin Asst Manager Desktop Admin etc. Application Office 2007 Office 2007 Office 2007 Internet Explorer Internet Explorer Internet Explorer I want to know the row where say Manager & Internet Explorer are the same. so no vlookup so categorize column a, find all the rows that = true, then do the same for b, and return the single row that has both as true.- Hide quoted text - - Show quoted text - On a separate sheet I have a column of Roles in column A (beginning at A2) and a row of Applications beginning at B1. Where the two intersect, I want to put an "X".- Hide quoted text - - Show quoted text - So I was thinking if i could get the row number, I could do a formula that says if the row number exists, then "X", othwerwise blank. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
| Matrix Lookup | Excel Discussion (Misc queries) | |||
| Excel Matrix lookup | Excel Discussion (Misc queries) | |||
| lookup in MATRIX | Excel Worksheet Functions | |||
| Lookup in Matrix | Excel Worksheet Functions | |||
Linear Mode
