Indirect / Match function used together

[Sorigel]

Alright maybe i am not to verse as i thought i was. I want to use an indirect cell reference as look up points for an index match deal. Easy you say well the cell reference is a table.

Sheet 1 contains Table named "LookUp"

In sheet 2:
Cell A1= "LookUp"

I want to write a index match that goes something like this.

=index(indirect(A1,0),match(D1,indirect(A1),0),match(E1,indirect(A1),0)

Now I know just writting (indirect A1) instead of an actualy array will not work. what i want to know is there a way to do this. In the match function I want it to be able to pick up on table1 lookup array.


I hope i am making sense because this is driving me nuts.

 

[WarPigl3t]

Forget about what you have done so far. Let's start over. You have Sheet1: A1 where you want to have your formula. Where is the lookup value? For now I'll assume the lookup value is in A2. Now you want to lookup the value in the table in another sheet. Let me assume that the lookup table is in Sheet2 in cells A1:A20. Now I'm going to assume that you want to return something else other than the lookup value. Maybe you want to return something like a product number or something. We'll say the product numbers are in Sheet2 in cells B1:B20. Am I on the right track? Be more specific in your desire and less specific on what your current formula is.

 

[Andrew Poulsom]

Does this work for you?

=INDEX(INDIRECT(A1),MATCH(D1,INDEX(INDIRECT(A1),0,1),FALSE),MATCH(E1,INDEX(INDIRECT(A1),1,0),FALSE))

 

[Sorigel]

Look up value is not what i am having trouble with. It is with Look Up Array. But to answer your question Look Up value is on the same sheet as the formula. And your assumption after that are correct. the table is on sheet two.

I want to pull the look up array from the table located on sheet 2.

Sheet 1 has a cell with the table's name. A1= "Look_UP".

I want my formula to look up the row and column array of the "Look_up" table. However, I don't want to just write the name of the table because the table name will change based on other criteria.

Thanks for the help!

 

[Andrew Poulsom]

Did you try the formula I posted?

 

[Sorigel]

Yes i get #REF! I feel though that might be on the right track though

 

[Andrew Poulsom]

Works for me. The name LookUp refers to H1:M6.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
LookUp	a
a
b
c
d
e

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]42[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]79[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]26[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]83[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]69[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]88[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]85[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]21[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]F1[/TH]
[TD="align: left"]=INDEX(INDIRECT(A1),MATCH(D1,INDEX(INDIRECT(A1),0,1),FALSE),MATCH(E1,INDEX(INDIRECT(A1),1,0),FALSE))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Sorigel]

OMG! This is exactly what I need. Can Look Up be a table's name instread of just a named range?

 

[Andrew Poulsom]

The syntax would be slightly different for a Table because the named range excludes the header row:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Table1			a	Col2	42		Col1	Col2	Col3	Col4	Col5	Col6
2								a	42	22	79	36	3
3								b	79	96	16	60	26
4								c	4	96	49	99	83
5								d	69	7	95	42	88
6								e	85	99	90	35	21
Sheet1

Cell Formulas
Range	Formula
F1	=INDEX(INDIRECT(A1),MATCH(D1,INDEX(INDIRECT(A1),0,1),FALSE),MATCH(E1,OFFSET(INDIRECT(A1),-1,0,1),FALSE))