Index a global array by value

[JenniferMurphy]

In Sheet1, I have a table like this but with many more rows.

C/R	C	D	E
6	Product	Price	Weight
7	M28	$29.95	0.35
8	M29	$19.95	0.22
9	M44	$49.95	3.50
10	S9	$9.95	1.25
11	S10	$10.95	1.50

<tbody>
</tbody>

Columns C, D, & E are assigned the global names "Product", "Price", & "Weight" respectively.

In another sheet in the same workbook, I want to create a table like this:

C/R	H	I	J	K
6	Product	Quan	Price	Cost
7	M44	5	$49.95	$249.75
8	S10	10	$10.95	$109.50
9	M28	25	$29.95	$748.75

<tbody>
</tbody>

where the values in column J are taken from the table in Sheet1.

Is there any way to do that?

Related question, is there a way to do this if Sheet1 is in a different workbook that is not currently open?

 

[Aladin Akyurek]

Sheet1

Row\Col	C​	D​	E​
6​	Product​	Price​	Weight​
7​	M28	$29.95 ​	0.35​
8​	M29	$19.95 ​	0.22​
9​	M44	$49.95 ​	3.5​
10​	S9	$9.95 ​	1.25​
11​	S10	$10.95 ​	1.5​

Name A6:E11 (thus headers included) PTable via the Name Box or FORMULAS | Name Manager.

Save the workbook as jm.xlsx (for example)...

1.

Sheet2

Row\Col	H​	I​	J​	K​
6​	Product​	Quan​	Price​	Cost​
7​	M44​	5​	$49.95 ​	$249.75 ​
8​	S10​	10​	$10.95 ​	$109.50 ​
9​	M28​	25​	$29.95 ​	$748.75 ​

In J7 enter and copy down:

=VLOOKUP($H7,PTable,MATCH(J$6,INDEX(PTable,1,0),0),0)

In K7 enter and copy down:

=I7*J7

2. Open a new workbook.

Sheet1 of the new workbook:

Row\Col	A​	B​	C​	D​
1​	Product​	Quan​	Price​	Cost​
2​	M44​	5​	$49.95 ​	$249.75 ​
3​	S10​	10​	$10.95 ​	$109.50 ​
4​	M28​	25​	$29.95 ​	$748.75 ​

In C2 enter and copy down:

=VLOOKUP($A2,jm.xlsx!PTable,MATCH(C$1,INDEX(jm.xlsx!PTable,1,0),0),0)

In D2 enter and copy down:

=B2*C2

 

[JenniferMurphy]

Sheet1

Row\Col	C​	D​	E​
6​	Product​	Price​	Weight​
7​	M28	$29.95 ​	0.35​
8 ​	M29	$19.95 ​	0.22​
9 ​	M44	$49.95 ​	3.5​
10 ​	S9	$9.95 ​	1.25​
11 ​	S10	$10.95 ​	1.5​

<tbody>
</tbody>

Name A6:E11 (thus headers included) PTable via the Name Box or FORMULAS | Name Manager.

Did you mean C6:E11?

Would it also work to define the named range as $C:$E? That way if I add rows to the table, I don't have to redefine the range.

Sheet2

Row\Col	H​	I​	J​	K​
6​	Product​	Quan​	Price​	Cost​
7​	M44​	5​	$49.95 ​	$249.75 ​
8​	S10​	10​	$10.95 ​	$109.50 ​
9​	M28​	25​	$29.95 ​	$748.75 ​

<tbody>
</tbody>

In J7 enter and copy down:

=VLOOKUP($H7,PTable,MATCH(J$6,INDEX(PTable,1,0),0),0)

In K7 enter and copy down:

=I7*J7

Very nice, thank you

2. Open a new workbook.

Sheet1 of the new workbook:

Row\Col	A​	B​	C​	D​
1​	Product​	Quan​	Price​	Cost​
2​	M44​	5​	$49.95 ​	$249.75 ​
3​	S10​	10​	$10.95 ​	$109.50 ​
4​	M28​	25​	$29.95 ​	$748.75 ​

<tbody>
</tbody>

In C2 enter and copy down:

=VLOOKUP($A2,jm.xlsx!PTable,MATCH(C$1,INDEX(jm.xlsx!PTable,1,0),0),0)

In D2 enter and copy down:

=B2*C2

Outstanding. Is this any less efficient than having it in the same workbook? I imagine Excel loads it once and then keeps it in memory.

 

[JenniferMurphy]

Sheet1
=VLOOKUP($A2,jm.xlsx!PTable,MATCH(C$1,INDEX(jm.xlsx!PTable,1,0),0),0)

PS: I assume that this requires the jm.xlsx book to be in the same folder as the calling book. If it is in a different folder, do I add the full path before the book name? Do I enclose it in quotes? What if the path contains spaces?

Thanks

 

[Aladin Akyurek]

Did you mean C6:E11?

Yes, that's what I had in mind.

Would it also work to define the named range as $C:$E? That way if I add rows to the table, I don't have to redefine the range.

That shoul be possible. Note that the MATCH expressions the formulas include expect the headers in the first row.

Very nice, thank you



Outstanding.

You are welcome.

Is this any less efficient than having it in the same workbook? I imagine Excel loads it once and then keeps it in memory.

Yeah. In the same book, there should be no need to have all that in memory I think.

PS: I assume that this requires the jm.xlsx book to be in the same folder as the calling book. If it is in a different folder, do I add the full path before the book name? Do I enclose it in quotes? What if the path contains spaces?

Thanks

Let Excel do all that... Keep both books open while you implement the formulas. Once the formulas in place, you can close the appropriate book.

 

[JenniferMurphy]

Let Excel do all that... Keep both books open while you implement the formulas. Once the formulas in place, you can close the appropriate book.

That works amazingly well. I created two workbooks: Book1 with a "source" table containing the master data. Book2 in a different folder containing an "access" table using the formulas you provided. The access table was able to pull any of the data from the source table. I then closed the access workbook, made some changes in the source table and closed it. I then opened the access table and the changes were immediately updated.

Then I got a bit of a surprise. I checked the formulas in the access table. It was humongous. As you said, Excel took care of adding the path to the filename. The resulting formula was 478 characters long! It works, but I would be very reluctant to touch it.

It looks like I have gone well past the database capabilities of Excel. I think it is time to learn Access.

Thanks for all the help.

 

[Aladin Akyurek]

That works amazingly well. I created two workbooks: Book1 with a "source" table containing the master data. Book2 in a different folder containing an "access" table using the formulas you provided. The access table was able to pull any of the data from the source table. I then closed the access workbook, made some changes in the source table and closed it. I then opened the access table and the changes were immediately updated.

Then I got a bit of a surprise. I checked the formulas in the access table. It was humongous. As you said, Excel took care of adding the path to the filename. The resulting formula was 478 characters long! It works, but I would be very reluctant to touch it.

It looks like I have gone well past the database capabilities of Excel. I think it is time to learn Access.

Or Power Pivot...

Thanks for all the help.

You are welcome.