VLOOKUP with multiple criteria

[crowncontent]

Hi,

Not sure if VLOOKUP is the proper function for this, but I am trying to generate a list based on multiple search results. Basically, I have a list of book purchases that look like this --

NAME --- Book Purchased
John --- Book A
John --- Book B
John --- Book C
Greg --- Book A
Greg --- Book C
Fred --- Book C
Mark --- Book A

What I would like done is arrange it in the following format --

Name --- Book A --- Book B --- Book C
John --- YES ------ YES ------ YES
Greg --- YES ------ NO ------- YES
Fred --- NO ------- NO ------- YES
Mark --- YES ------ NO ------- NO

I was trying to use VLOOKUP but it would only return the first value seen. Is there a way I can make it search the entire listing, then return a "YES" or "NO" if a particular person has purchased a book?

 

[markmzz]

Try this array formula (use Ctrl+Shift+Enter and not only Enter):


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">NAME </TD><TD style="FONT-WEIGHT: bold">Book Purchased</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Name </TD><TD style="FONT-WEIGHT: bold">Book A</TD><TD style="FONT-WEIGHT: bold">Book B</TD><TD style="FONT-WEIGHT: bold">Book C</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>John </TD><TD>Book A</TD><TD style="TEXT-ALIGN: right"></TD><TD>John </TD><TD>YES</TD><TD>YES</TD><TD>YES</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>John </TD><TD>Book B</TD><TD style="TEXT-ALIGN: right"></TD><TD>Greg </TD><TD>YES</TD><TD>NO</TD><TD>YES</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>John </TD><TD>Book C</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fred </TD><TD>NO</TD><TD>NO</TD><TD>YES</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Greg </TD><TD>Book A</TD><TD style="TEXT-ALIGN: right"></TD><TD>Mark </TD><TD>YES</TD><TD>NO</TD><TD>NO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Greg </TD><TD>Book C</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Fred </TD><TD>Book C</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Mark </TD><TD>Book A</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>******</TD><TD>***************</TD><TD style="TEXT-ALIGN: right"></TD><TD>******</TD><TD>******</TD><TD>******</TD><TD>******</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">{=IF(SUM(($A$2:$A$8=$D2)*($B$2:$B$8=E$1))>0,"YES","NO")}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>


Markmzz

 

[snoopyhr]

THis is just in case you want to create the desired table from existing witout retyping the column and row labels.

<b>**Copy the Cell F2 to H2 , Copy the Cell E3 to E6, copy Cell F3 to H6 **</b>

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	*	*	*	*****	*	*	*
2	NAME	Book Purchased	*	*	Name	Book A	Book B	Book C
3	John	Book A	*	*	John	yes	yes	yes
4	John	Book B	*	*	Greg	yes	no	yes
5	John	Book C	*	*	Fred	no	no	yes
6	Greg	Book A	*	*	Mark	yes	no	no
7	Greg	Book C	*	*	*	*	*	*
8	Fred	Book C	*	*	*	*	*	*
9	Mark	Book A	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*
Sheet

 

[bosco_yip]

Please refer to Markmzz;s formula, if you don't wanted to use array formula,

you can use SUMPRODUCT function instead of SUM funtion :

=IF(SUMPRODUCT(($A$2:$A$8=$D2)*($B$2:$B$8=E$1)),"YES","NO")

Regards

 

[crowncontent]

BIG THANKS to everyone who helped out with this. Works like a charm!

 

[markmzz]

BIG THANKS to everyone who helped out with this. Works like a charm!

Thanks for the feedback.

Markmzz