VLOOKUP, ARRAY, ROW, COLUMN please help!!!

Question

I&nbsp;need&nbsp;a formula that will search the item&nbsp;#&nbsp;from table 1&nbsp;in table 2 and&nbsp;display the number for each individual&nbsp;category ("inca", "ineb", etc.)&nbsp;The lines that do not have sales&nbsp;I want it to display a blank. For example, line 6 in table 1 should take that item # and find it in&nbsp;table 2 and display the information for each category when it matches. &nbsp;Table #1Table #2 (Pivot table)

karla ruelas · Answer

sorry I reposted it to clarify some stuff!

man fai chan · Answer

Is the following formula help:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF($J2&gt;0,VLOOKUP($A2,Table_2!$A:$K, COLUMN(B2),FALSE),"")&nbsp;I assume that the table 2 is stored in a sheet called &lt;Table_2&gt;. However, I notice that your table_2 is a pivot table, I am not sure it works properly.

wyn hopkins · Answer

Hi&nbsp;Karla Ruelas,
&nbsp;
A SUMIFS formula should solve your problem without the potential #N/A issue from a VLOOOKUP
&nbsp;
I've assumed your Pivot Table is on a sheet called Sheet2
&nbsp;
=IF(&nbsp; &nbsp;I2="No","",&nbsp; SUMIFS( Sheet2!$B:$B,&nbsp; Sheet2!A:A,&nbsp; $A2)&nbsp; &nbsp;)
&nbsp;
&nbsp;

wyn hopkins · Answer

Also, when "looking up" from a Pivot Table I'd recommend the attached approach since Pivot Table columns can change and formulas can pull through the wrong data without you even realising
&nbsp;
You create 2 helper formulas (a column match and a row match.&nbsp; Then your INDEX formula just brings back the value from the cell with those coordinates.
&nbsp;
This formula then still works correctly even if your Pivot table columns are re-ordered or new one is added / disappears or if a new column is inserted in column A of your Pivot Table sheet.&nbsp;&nbsp;
&nbsp;

wyn hopkins · Answer

Hi Man Fai Chan,Just a suggestion that the reference to COLUMN(B2)  could be quite a risky approach as if a new column is inserted to the left of COLUMN B then the formula will still work but the wrong results will be returned.  If using this approach then you should reference COLUMN(Table_2!B:B) to be a bit safer.  However this also will go wrong if someone inserts a column in Table_2 column A.I'd go with SUMIFS or INDEX MATCH to be a safer.Just talking from learning this the hard way :)

Forum Discussion

VLOOKUP, ARRAY, ROW, COLUMN please help!!!

6 Replies