multiple vlookup values issue

[jtsains]

I need some help. I need to use a vlookup to find an item number in a column and bring back the name of the vendor, only when a different column value is "Y".

For this example, I am entering my item number in cell A3 and I want it to retrieve the value in Column F only when Column E = 1. Since there are multiple item number 98 it tries to only give me the result of "Kraft" when what I really want is "Generic".

Please help.

<TABLE style="WIDTH: 365pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=485><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 height=20 width=64>1</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 width=64>A</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 width=64>B</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 width=64>C</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 width=74>D</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 width=61>E</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 width=94>F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 height=20>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Result</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Validated?</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Data set</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Vendor Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 height=20>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>98</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>John Deer</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 height=20>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>62</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Spalding</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 height=20>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>98</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Kraft</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl65 height=20>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>98</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Generic</TD></TR></TBODY></TABLE>

 

[T. Valko]

I am using Excel 2007
there will not always be a value in item number and therefore should = "" when item number is null and if item number doesn't exist or doesn't match i need it to say "invalid"

Thanks
Jeff

Maybe this...

Array entered**:

=IF(A2="","",IFERROR(INDEX(E2:E5,MATCH(1,IF(C2:C5=A2,IF(D2:D5="Y",IF(F2:F5=1,1))),0)),"Invalid"))

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

 

[jtsains]

This WORKS!!!

Thank you so much for all your help!!

Jeff

 

[T. Valko]

This WORKS!!!

Thank you so much for all your help!!

Jeff

You're welcome. Thanks for the feedback!