Why cant i refer with INDEX and MATCH to a table header

It should work fine. But we can't see what YOU have done.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

Follow the step for putting range ( Array for Index & Lookup array for Match)

Whenever you will need to select the range in table you just need to go to the column and then press Control+Spence bar+Spence bar+Spence bar three time Spence bar. This will select entire column as a range and you will not bother for understanding range.

If it helps you then its okay other wise please attached a sample workbook with expected result.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Try

B14=INDEX(Table1[#All],MATCH([@Name],$A$4:$A$7,0),MATCH(B$12&"",Table1[#Headers],0))

May be

=INDEX(Table1[#All],MATCH([@Name],Table1[[#All],[Name]],0),MATCH(B$12&"",Table1[#Headers],0))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Please add reputation by clicking star * left corner on poster solution which helps you.

Originally Posted by esahakos

By the way can you lock [@Name] some way as you lock wit F4 and you get the $$ signs
or is there another way to lock the cell inside a table

Hi,

If you wish to lock the column reference so as to be able to fill the formula across all columns, you may use
=INDEX(Table1[#All],MATCH(Table2[@[Name]:[Name]],$A$4:$A$7,0),MATCH(B$12&"",Table1[#Headers],0))

Alternatively, if you copy and paste the formula, or Ctl+Enter it into all columns at once, the column reference will not change.