Matrix Type Lookup

  • Thread starter Thread starter Search & You Will Find
  • Start date Start date
S

Search & You Will Find

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.
 
So what result are you looking for? The actual row number?

--
Biff
Microsoft Excel MVP












- Show quoted text -
Click to expand...

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".
 
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 -
Click to expand...

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.
 
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


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 -
Click to expand...

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.
 
Back
Top