Vlookup and Counta

[grinch76]

I need a formula for sheet1 that will equal all text that is shown among columns P:U in sheet2 (if blank "") and referencing the number in column A for vlookup. any ideas?

Book1.xlsx

[samba_ravi]

please attach sample excel file
Press Alt X
attach file.png

[grinch76]

File Attached.

[samba_ravi]

B2=LOOKUP("ZZZZZZZZZZZZZZ",INDEX(Sheet2!$B$2:$G$7,MATCH($A2,Sheet2!$A$2:$A$7,0),),INDEX(Sheet2!$B$2:$G$7,MATCH($A2,Sheet2!$A$2:$A$7,0),))
TRY THIS AND COPY TOWARDS DOWN
IF IT IS NOT YOUR ARE LOOKING FOR GIVE YOUR EXPECTED RESULT MANUALLY

[bebo021999]

Try in B2:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[samba_ravi]

=COUNTA(INDEX(Sheet2!$B$2:$G$7,MATCH($A2,Sheet2!$A$2:$A$7,0),))

[grinch76]

The first formula seemed to work on my example file but not on the one I'm working on. I've attached a new file to resemble closer the cells used. Also is there a way for the target cell to list more than one season? Perhaps separated with a comma?

Book2.xlsx

[grinch76]

I'm probably asking too much...
The formula below worked but if you could help me adjust the formula for the expanded range in Book2 attached above.

B2=LOOKUP("ZZZZZZZZZZZZZZ",INDEX(Sheet2!$B$2:$G$7,MATCH($A2,Sheet2!$A$2:$A$7,0),),INDEX(Sheet2!$B$2:$G$7,MATCH($A2,Sheet2!$A$2:$A$7,0),))

[grinch76]

Still banging my head on this one...

[samba_ravi]

b2=IFERROR(LOOKUP("ZZZZZZZZZZZZZZ",INDEX(IB!$P$2:$U$7000,MATCH($A2,IB!$A$2:$A$7000,0),)),"")
try this and copy towards down

[grinch76]

The problem here is that the Skus on the sheet IB may not be in the same order as sheet1. so using this formula is showing the wrong value. It needs to lookup with column A.

[grinch76]

COPY New Product & Seasonals on Floor.xlsx

Ok. New file attached. This is the actual file cleaned up. I think you'll see what I mean. Thanks again for your assistance.

[samba_ravi]

Where it showing wrong answer, and what is your desired result and why