lookup on 2 valus

T

terry freedman

Hi, hope someone can help.

I have the following data in columns A and B:

John Project A
John Project B
Fred Project A
Fred Project C
etc

what I would like to do is create a matrix that looks like this:

Project A Project B Project C etc
John yes yes
Fred yes yes
etc

Is there a formula I can put in in order to egnerate the "yeses"?

thanks very much in advance
Terry
==
Terry Freedman
For articles on e-learning and ICT, and to subscribe to Computers in Classrooms, visit http://www.ictineducation.org
 
A

Anson

You could do a vlookup on 2 values here is how:
First, in your first table add a column and concatenate the columns with the names and the project names (e.g. "=A4&B4"). Insert another column just type in "Yes" to each corresponding record at the end, it should go something like this:

Column
A B C D
John Project A JohnProject A Yes
John Project B JohnProject B Yes
Fred Project A FredProject A Yes
Fred Project C FredProject C Yes


Prepare row and column headings with the unique entries of names and project names
Enter the following formula (assuming your column heading is A and row heading is 1).
"=Vlookup($A2&B$1,[lookup range as columns C & D],2,false)"

Finally clear the formulae by copy>pastespecial>values and use "Find and Replace" to get rid of the "#N/A"s.

Hope this helps
 
T

terry freedman

That worked a treat! Thanks, Anson!

You could do a vlookup on 2 values here is how:
First, in your first table add a column and concatenate the columns with the names and the project names (e.g. "=A4&B4"). Insert another column just type in "Yes" to each corresponding record at the end, it should go something like this:

Column
A B C D
John Project A JohnProject A Yes
John Project B JohnProject B Yes
Fred Project A FredProject A Yes
Fred Project C FredProject C Yes


Prepare row and column headings with the unique entries of names and project names
Enter the following formula (assuming your column heading is A and row heading is 1).
"=Vlookup($A2&B$1,[lookup range as columns C & D],2,false)"

Finally clear the formulae by copy>pastespecial>values and use "Find and Replace" to get rid of the "#N/A"s.

Hope this helps

terry freedman said:
Hi, hope someone can help.

I have the following data in columns A and B:

John Project A
John Project B
Fred Project A
Fred Project C
etc

what I would like to do is create a matrix that looks like this:

Project A Project B Project C etc
John yes yes
Fred yes yes
etc

Is there a formula I can put in in order to egnerate the "yeses"?

thanks very much in advance
Terry
==
Terry Freedman
For articles on e-learning and ICT, and to subscribe to Computers in Classrooms, visit http://www.ictineducation.org
Click to expand...
Click to expand...

==
Terry Freedman
For articles on e-learning and ICT, and to subscribe to Computers in Classrooms, visit http://www.ictineducation.org
 
Top