# Indirect / Match function used together

#### Sorigel

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
Did you try the formula I posted?

#### Sorigel

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

Last edited:

#### Andrew Poulsom

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

Excel 2010
ABCDEFGHIJKLM
1LookUpa14212345
2a422279363
3b7996166026
4c496499983
5d697954288
6e8599903521

</TBODY>
Sheet1

Worksheet Formulas
CellFormula
F1=INDEX(INDIRECT(A1),MATCH(D1,INDEX(INDIRECT(A1),0,1),FALSE),MATCH(E1,INDEX(INDIRECT(A1),1,0),FALSE))

</TBODY>

<TBODY>
</TBODY>

#### Sorigel

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

#### Andrew Poulsom

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