Hi,
I've been trying to do an index/if/row/column formula and I can't get it to work. I've attached the workbook in question. On the "page" tab, I have a list of items that are sorted by velocity. In cell C2, I need a formula that will lookup the value in cell A2 and return multiple results in cells C2, D2, E2, etc. from the data in the "Data" tab.
Example is shown in cell C3-H3.
Thanks!
Chad
Hi Chad- Full-column references and array formulas don't play well together, so I used named ranges (which can be made dynamic).
Item=Data!$C$2:$C$16508
Location=Data!$D$2:$D$16508
Paste this ARRAY FORMULA* in C2 and copy down:*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
Please Login or Register to view this content.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.
Last edited by leelnich; 10-19-2017 at 05:48 PM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
Here are the dynamic versions of the named ranges. Select Name Manager on the Formula tab and change the refers to ranges to:
Item = Data!$C$1:INDEX(Data!$C$1:$C$80000,COUNTA(Data!$C$1:$C$80000))
Location = Data!$D$1:INDEX(Data!$D$1:$D$80000,COUNTA(Data!$C$1:$C$80000))
These assume NO BLANKS in Data!column C.
HERE is a link to a good description of Dynamic Named Ranges.
Last edited by leelnich; 10-19-2017 at 06:09 PM.
You are the greatest!!!!
Happy to help! BTW, you need to extend the formula range out a few more columns; some SKUs need the extra space.
If your requirements have been met, please mark your thread as SOLVED (Thread Tools up top).Thanks -Lee
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks