multiple vlookup values issue

J

jtsains

Board Regular
Joined
Apr 29, 2011
Messages
103
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.
Rich (BB code): 
<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>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
I just realized my Vendor Name column is before the Data Set Column, therefore the Vlookup would have to be able to pull data from the column before the value it is looking for validation.

Code: 
<TABLE dir=ltr border=1 cellSpacing=1 borderColor=#000000 cellPadding=2 width=521><TBODY><TR><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]1[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]A[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]B[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]C[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="15%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]D[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]E[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="19%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]F[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD></TR><TR><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]2[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Item[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Result[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Item[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="15%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Validated?[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Vendor Name[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="19%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Data Set[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD></TR><TR><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]3[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]62[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]45[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="15%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Y[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]John Deer[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="19%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]1[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD></TR><TR><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]4[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">
</TD><TD height=19 width="13%">
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]62[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="15%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Y[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Spalding[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="19%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]0[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD></TR><TR><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]5[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">
</TD><TD height=19 width="13%">
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]98[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="15%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Y[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Kraft[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="19%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]0[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD></TR><TR><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]6[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">
</TD><TD height=19 width="13%">
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]98[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="15%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Y[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="13%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]Generic[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD><TD height=19 width="19%">[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3][CENTER]1[/SIZE][/FONT][/SIZE][/FONT][/CENTER]
</TD></TR></TBODY></TABLE>

Is it possible to look at column F for validation and then pull back column E?
 
Upvote 0
Hi,

Try this Array-formula in B3
=INDEX($E$2:$E$5;SMALL(IF($C$2:$C$5=$A3;IF($F$2:$F$5=1;ROW($E$2:$E$5)-ROW($E$2)+1));ROWS($1:1)))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift ket and hit Enter)

HTH

M.
 
Upvote 0
Or 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></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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">Item</TD><TD style="TEXT-ALIGN: center">Result</TD><TD style="TEXT-ALIGN: center">Item</TD><TD style="TEXT-ALIGN: center">Validated?</TD><TD style="TEXT-ALIGN: center">Vendor Name</TD><TD style="TEXT-ALIGN: center">Data Set</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">98</TD><TD style="TEXT-ALIGN: center">Generic</TD><TD style="TEXT-ALIGN: center">45</TD><TD style="TEXT-ALIGN: center">Y</TD><TD style="TEXT-ALIGN: center">John Deer</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">62</TD><TD style="TEXT-ALIGN: center">Y</TD><TD style="TEXT-ALIGN: center">Spalding</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">98</TD><TD style="TEXT-ALIGN: center">Y</TD><TD style="TEXT-ALIGN: center">Kraft</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">98</TD><TD style="TEXT-ALIGN: center">Y</TD><TD style="TEXT-ALIGN: center">Generic</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</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">7</TD><TD>*******</TD><TD>*******</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>B2</TH><TD style="TEXT-ALIGN: left">{=INDEX($E$2:$E$5,LARGE(($C$2:$C$5=A2)*($F$2:$F$5=1)*(ROW($E$2:$E$5)-ROW($E$1)),1))}</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
 
Upvote 0
jtsains said:
I just realized my Vendor Name column is before the Data Set Column, therefore the Vlookup would have to be able to pull data from the column before the value it is looking for validation.

Code: 
[CENTER]<TABLE dir=ltr borderColor=#000000 cellSpacing=1 cellPadding=2 width=521 border=1><TBODY><TR><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]1[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]A[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]B[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]C[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="15%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]D[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]E[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="19%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]F[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD></TR><TR><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]2[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Item[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Result[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Item[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="15%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Validated?[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Vendor Name[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="19%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Data Set[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD></TR><TR><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]3[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]62[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]45[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="15%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Y[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]John Deer[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="19%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]1[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD></TR><TR><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]4[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]62[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="15%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Y[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Spalding[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="19%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]0[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD></TR><TR><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]5[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]98[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="15%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Y[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Kraft[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="19%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]0[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD></TR><TR><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]6[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]98[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="15%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Y[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="13%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Generic[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD><TD width="19%" height=19>[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]1[/SIZE][/FONT][/SIZE][/FONT]
 
[CENTER][/CENTER]
</TD></TR></TBODY></TABLE>[/CENTER]

Is it possible to look at column F for validation and then pull back column E?
Click to expand...
I'm sure this can be done but you need to figure out what it is you want to do!

In your first post you say:

bring back the name of the vendor, only when a different column value is "Y".
Click to expand...
But then you say:
I want it to retrieve the value in Column F only when Column E = 1.
Click to expand...
And in your 2nd post you say:
Is it possible to look at column F for validation and then pull back column E?
Click to expand...
But the "validated" column looks like it's column D.

So, if you tell us with certainty what you want to do I'm sure we can get it done.
 
Upvote 0
Biff,

I am sorry if my explanation is fuzzy. I need both the Y and the 1 to be valid before bringing back a value. If Y is other than Y (Which I didn't give as an option on the sheet, then there must also be a 1 in the Data Set column before bringing back the vendor name.

If anyone can explain the array fromula to me it would help me out tremendously when adding formulas in the future.


The Array formulas don't seem to work. The first one gives me an error message and the second gives the answer of John Deer.

Also, i don't know what you mean by using CTR+SHIFT+Enter. I tried copying the formula and using this method, but nothing happens.

Thanks
Jeff
 
Last edited:
Upvote 0
jtsains said:
Biff,

I am sorry if my explanation is fuzzy. I need both the Y and the 1 to be valid before bringing back a value. If Y is other than Y (Which I didn't give as an option on the sheet, then there must also be a 1 in the Data Set column before bringing back the vendor name.

If anyone can explain the array fromula to me it would help me out tremendously when adding formulas in the future.


The Array formulas don't seem to work. The first one gives me an error message and the second gives the answer of John Deer.

Also, i don't know what you mean by using CTR+SHIFT+Enter. I tried copying the formula and using this method, but nothing happens.

Thanks
Jeff
Click to expand...
Try this...

Book1
ABCDEF
1ItemResultItemValidatedVendorData Set
298Generic45YJohn Deer1
3__62YSpalding0
4__98YKraft0
5__98YGeneric1
Sheet1

This array formula** entered in B2:

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

** 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.
 
Upvote 0
Thank you so much!! I figured out that you have to enter the formula and then press CTR+SHIFT+Enter after the formula is entered. I had never worked with array formulas so wasn't aware of this.


One additiona questions:

If Validation = N or Data set = 0 can I tell it to provide a result of "Invalid" as a text?

Thanks for your help, this is so awesome!!!
Jeff
 
Upvote 0
jtsains said:
Thank you so much!! I figured out that you have to enter the formula and then press CTR+SHIFT+Enter after the formula is entered. I had never worked with array formulas so wasn't aware of this.


One additiona questions:

If Validation = N or Data set = 0 can I tell it to provide a result of "Invalid" as a text?

Thanks for your help, this is so awesome!!!
Jeff
Click to expand...
Do you mean that if we lookup item 98 and either validated=N or Data=0 to return "Invalid" otherwise return the appropriate vendor?

If that's what you want:
  • What version of Excel are you using?
  • Will the lookup item number always be present? In other words, if we want to lookup item 98, will there always be an item 98 to be found?
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,613
Messages
6,179,903
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top