VLOOKUP and then IF

[milikien]

Hello, I’m working on the same report as yesterday. Does anyone have a suggestion on how I can pull a value by looking up (VLOOKUP) a name and if (IF) they have a certain product? Again, not sure how to describe it or if there is a name for it. Here is what I have so far:

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
___A_______B________C___<o></o>
Name #1 Product #1 $Amount
Name #1 Product #2 $Amount
Name #1 Product #3 $Amount
Name #2 Product #1 $Amount
Name #2 Product #3 $Amount
Name #3 Product #2 $Amount
Name #3 Product #4 $Amount
Name #3 Product #5 $Amount
Name #3 Product #6 $Amount
Name #4 Product #1 $Amount
Name #4 Product #4 $Amount

<o></o>
=IFERROR(VLOOKUP(“Name”,'Data'!$A:$C,(IF('Data'!B1="Product #1",'Data'!C1," ")),FALSE),"Not Found")<o></o>
<o></o>
English – Look up a name and if they have Product __, then show the amount; if not, blank<o></o>

Please note that not all names have the same products. Thanks for any suggestions. <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=Picture_x0020_1 style="VISIBILITY: visible; WIDTH: 11.25pt; HEIGHT: 11.25pt; mso-wrap-style: square" alt="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="icon_smile" src="file:///C:\DOCUME~1\CMILIK~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape>

 

[jimrward]

you could add in a helper column joining name and product and use this in the lookup

 

[Aladin Akyurek]

Hello, I’m working on the same report as yesterday. Does anyone have a suggestion on how I can pull a value by looking up (VLOOKUP) a name and if (IF) they have a certain product? Again, not sure how to describe it or if there is a name for it. Here is what I have so far:

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
___A_______B________C___<o></o>
Name #1 Product #1 $Amount
Name #1 Product #2 $Amount
Name #1 Product #3 $Amount
Name #2 Product #1 $Amount
Name #2 Product #3 $Amount
Name #3 Product #2 $Amount
Name #3 Product #4 $Amount
Name #3 Product #5 $Amount
Name #3 Product #6 $Amount
Name #4 Product #1 $Amount
Name #4 Product #4 $Amount

<o></o>
=IFERROR(VLOOKUP(“Name”,'Data'!$A:$C,(IF('Data'!B1="Product #1",'Data'!C1," ")),FALSE),"Not Found")<o></o>
<o></o>
English – Look up a name and if they have Product __, then show the amount; if not, blank<o></o>

Please note that not all names have the same products. Thanks for any suggestions. <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=Picture_x0020_1 style="VISIBILITY: visible; WIDTH: 11.25pt; HEIGHT: 11.25pt; mso-wrap-style: square" alt="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="icon_smile" src="file:///C:\DOCUME~1\CMILIK~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape>

Control+shift+enter, not just enter:

=IFERROR(INDEX(AmountRange,MATCH(1,IF(NameRange=Name,IF(ProductRange=Product,1)),0)),"")

 

[T. Valko]

Hello, I’m working on the same report as yesterday. Does anyone have a suggestion on how I can pull a value by looking up (VLOOKUP) a name and if (IF) they have a certain product? Again, not sure how to describe it or if there is a name for it. Here is what I have so far:

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
___A_______B________C___<o></o>
Name #1 Product #1 $Amount
Name #1 Product #2 $Amount
Name #1 Product #3 $Amount
Name #2 Product #1 $Amount
Name #2 Product #3 $Amount
Name #3 Product #2 $Amount
Name #3 Product #4 $Amount
Name #3 Product #5 $Amount
Name #3 Product #6 $Amount
Name #4 Product #1 $Amount
Name #4 Product #4 $Amount

<o></o>
=IFERROR(VLOOKUP(“Name”,'Data'!$A:$C,(IF('Data'!B1="Product #1",'Data'!C1," ")),FALSE),"Not Found")<o></o>
<o></o>
English – Look up a name and if they have Product __, then show the amount; if not, blank<o></o>

Please note that not all names have the same products. Thanks for any suggestions. <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"></vath><o:lock v:ext="edit" aspectratio="t"></o:lock></v:shapetype><v:shape id=Picture_x0020_1 style="VISIBILITY: visible; WIDTH: 11.25pt; HEIGHT: 11.25pt; mso-wrap-style: square" type="#_x0000_t75" o:spid="_x0000_i1025" alt="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif"><v:imagedata src="file:///C:\DOCUME~1\CMILIK~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif" o:title="icon_smile"></v:imagedata></v:shape>

If the value to be returned is numeric and the combination of name + product is unique then you can use something like this.


Use cells to hold the criteria:

• E2 = some name
• F2 = some product

Then:

=SUMIFS(C2:C10,A2:A10,E2,B2:B10,F2)