Indirect / Match function used together

Sorigel

New Member
Joined
Nov 27, 2012
Messages
10
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
Joined
May 25, 2014
Messages
1,599
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Nov 27, 2012
Messages
10
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!
 

Sorigel

New Member
Joined
Nov 27, 2012
Messages
10
Yes i get #REF! I feel though that might be on the right track though
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Works for me. The name LookUp refers to H1:M6.

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

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</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))

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>
 

Sorigel

New Member
Joined
Nov 27, 2012
Messages
10
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
Joined
Jul 21, 2002
Messages
73,092
The syntax would be slightly different for a Table because the named range excludes the header row:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Table1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a</td><td style=";">Col2</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td><td style=";">Col1</td><td style=";">Col2</td><td style=";">Col3</td><td style=";">Col4</td><td style=";">Col5</td><td style=";">Col6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a</td><td style="text-align: right;;">42</td><td style="text-align: right;;">22</td><td style="text-align: right;;">79</td><td style="text-align: right;;">36</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">b</td><td style="text-align: right;;">79</td><td style="text-align: right;;">96</td><td style="text-align: right;;">16</td><td style="text-align: right;;">60</td><td style="text-align: right;;">26</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c</td><td style="text-align: right;;">4</td><td style="text-align: right;;">96</td><td style="text-align: right;;">49</td><td style="text-align: right;;">99</td><td style="text-align: right;;">83</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">d</td><td style="text-align: right;;">69</td><td style="text-align: right;;">7</td><td style="text-align: right;;">95</td><td style="text-align: right;;">42</td><td style="text-align: right;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">e</td><td style="text-align: right;;">85</td><td style="text-align: right;;">99</td><td style="text-align: right;;">90</td><td style="text-align: right;;">35</td><td style="text-align: right;;">21</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F1</th><td style="text-align:left">=INDEX(<font color="Blue">INDIRECT(<font color="Red">A1</font>),MATCH(<font color="Red">D1,INDEX(<font color="Green">INDIRECT(<font color="Purple">A1</font>),0,1</font>),FALSE</font>),MATCH(<font color="Red">E1,OFFSET(<font color="Green">INDIRECT(<font color="Purple">A1</font>),-1,0,1</font>),FALSE</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,085,294
Messages
5,382,766
Members
401,804
Latest member
RB85

Some videos you may like

This Week's Hot Topics

Top