Formula that will Look for a value in a table column that is on another worksheet...

[sspatriots]

This one will be tough to explain.

I have a worksheet called "PO BLK HIST IMP Data". On this worksheet I have several similar tables. Let's just start with one of the tables because I believe that the solution for one will work for all. Let's use the table named "tblTable4711" for this example. In table "tblTable4711" I have a column called "PO #" and another called "JL SHPG ARR/ITEM LCTN". I need to find the corresponding item location (populating the "JL SHPG ARR/ITEM LCTN" column) for each "PO #" in this table from a different table on a different worksheet called "Jobs".

The table name on the source worksheet is named "G2JobList". There are 18 different columns on this table that have Purchase Order Numbers with the 18 different corresponding columns of Item Locations for those Purchase Order Numbers. I started piecing a formula together as shown below just to test out using only three of the columns, but all the results are "#N/A". So, I'm pretty sure that using the choose array formula isn't the way to make this work. Any help would be greatly appreciated.

I have an example file that I'd like to attach here, but do not know where or how to do that. The file name is "SS Example.xlsx". I put that question in the lounge area, but maybe that isn't where I should have made that question. If someone could assist with this, I could provide the example file.


Thanks, SS

 

[sspatriots]

Just saw that I cannot attach a file in this forum. I must have been thinking of a forum I was in long ago where I did that. Below are a couple of watered down sample screenshots of the tables that I have. Sorry for the confusion, SS

The formula in that light red shaded cell that I tried and didn't work is as follows:

=INDEX(CHOOSE({1,2,3},G2JobList[Jack
SHPG ARR/
ITEM LCTN],G2JobList[Machine
SHPG ARR/
ITEM LCTN],G2JobList[Safety
SHPG ARR/
ITEM LCTN]),MATCH([@[PO '#]],CHOOSE({1,2,3},G2JobList[Jack
PO],G2JobList[Machine
PO],G2JobList[Safety
PO]),0))

 

[sspatriots]

Well I attempted to just stack these columns into a single column on a separate worksheet using the VSTACK function. The formula I tried is below. However, while I can select each array in the formula bar and hit the F9 key to see that the data for each column array is there, the worksheet where I placed the formula is totally blank. I can even see the outline of the entire array range boxed in with grey lines on the worksheet and the formula itself is greyed out from the cell just below all the way down. Perhaps someone could tell my the array isn't visible on the worksheet.

=VSTACK(G2JobList[Jack
PO],G2JobList[Machine
PO],G2JobList[Safety
PO])

If I change the formula to the following, I get the three headers only showing up:

=VSTACK(G2JobList[[#Headers],[Jack
PO]],G2JobList[[#Headers],[Machine
PO]],G2JobList[[#Headers],[Safety
PO]])

If I put either of these formulas into a new table, everything shows up as "#SPILL"



Thanks, SS

 

[RoryA]

However, while I can select each array in the formula bar and hit the F9 key to see that the data for each column array is there, the worksheet where I placed the formula is totally blank

Based on your description, and the pictures, those columns are empty, so it's not surprising you get no values back.

 

[sspatriots]

Based on your description, and the pictures, those columns are empty, so it's not surprising you get no values back.

There are entries in all of those columns. The small screenshot doesn't show any under "Jack PO" but it does show them under the other two columns.

 

[sspatriots]

I've figure it out. I had so many blanks at the top when I would just scroll down a bit I didn't see anything. So I used the "Ctrl + Down Arrow" to get to the bottom of the list and still didn't see any data when it took me to the bottom. All of the data didn't start until row 864 out of 1614 total rows for each column. So I just wasn't seeing anything. However, it looks like it is all there. Thanks for taking a look @RoryA. You got me thinking a bit when you said what you said.


Regards, SS