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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
Does this work for you?

=INDEX(INDIRECT(A1),MATCH(D1,INDEX(INDIRECT(A1),0,1),FALSE),MATCH(E1,INDEX(INDIRECT(A1),1,0),FALSE))
 
Upvote 0
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!
 
Upvote 0
Yes i get #REF! I feel though that might be on the right track though
 
Last edited:
Upvote 0
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>
 
Upvote 0
OMG! This is exactly what I need. Can Look Up be a table's name instread of just a named range?
 
Upvote 0
The syntax would be slightly different for a Table because the named range excludes the header row:


Excel 2010
ABCDEFGHIJKLM
1Table1aCol242Col1Col2Col3Col4Col5Col6
2a422279363
3b7996166026
4c496499983
5d697954288
6e8599903521
Sheet1
Cell Formulas
RangeFormula
F1=INDEX(INDIRECT(A1),MATCH(D1,INDEX(INDIRECT(A1),0,1),FALSE),MATCH(E1,OFFSET(INDIRECT(A1),-1,0,1),FALSE))
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top