Best way to find data from a table using Cell Ref and Headers

Bravo2003

Active Member
Joined
Apr 1, 2010
Messages
271
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I'm having a blank brain day... I'm sure I've done this before, but can't think what way worked best.
I have a master data table (Full List Master) with item names in Column A, then various headers across the table columns B-BZ
with all the relevant data in the rows below.

In a new sheet i want to create a smaller result table that matches against the name in Column A (lets choose Cell A10) and then search for the result from the column that matches the new sheets column header (lets make that ref 2020)

I could use - =INDEX('MD - Asset Type'!G:G,MATCH($E2,Asset_MS_Ref,0))
but that needs to choose the specific column (G:G) for every header i need...

Is there a simpler option to use I have forgotten, or not learnt properly yet?

TIA
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,767
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
=INDEX('MD - Asset Type'!B:BZ,MATCH($E2,Asset_MS_Ref,0),MATCH(2020,'MD - Asset Type'!B1:BZ1,0))
 

Bravo2003

Active Member
Joined
Apr 1, 2010
Messages
271
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
=INDEX('MD - Asset Type'!B:BZ,MATCH($E2,Asset_MS_Ref,0),MATCH(2020,'MD - Asset Type'!B1:BZ1,0))
Thanks, I updated my profile - it's Windows O365

I may need to work on this as an option, I may have the points in the wrong order... I tried using this, but it gave me a #N/A.. :(

=INDEX(Master_List[#All],MATCH($A11,Master_List[[#Headers],[NAME]],0),MATCH(Sheet2!BK3,Master_List[#Headers],0))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,767
Office Version
  1. 365
Platform
  1. Windows
Is the value in BK3 numeric or text?
 

Bravo2003

Active Member
Joined
Apr 1, 2010
Messages
271
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is the value in BK3 numeric or text?
This is the 2020 reference it needs to look to match - currently it is set as General, it is a straight copy paste of the header in the MasterTable
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,767
Office Version
  1. 365
Platform
  1. Windows
In that case try
Excel Formula:
=INDEX(Master_List[#All],MATCH($A11,Master_List[[#Headers],[NAME]],0),MATCH(Sheet2!BK3&"",Master_List[#Headers],0))
as table headers are text
 

Bravo2003

Active Member
Joined
Apr 1, 2010
Messages
271
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case try
Excel Formula:
=INDEX(Master_List[#All],MATCH($A11,Master_List[[#Headers],[NAME]],0),MATCH(Sheet2!BK3&"",Master_List[#Headers],0))
as table headers are text
Not sure if the error is related to the - Master_List[[#Headers],[NAME]] part as the error review shows it is only using cell $a$1 and not the full column?
 

Attachments

  • Capture.JPG
    Capture.JPG
    46.4 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,767
Office Version
  1. 365
Platform
  1. Windows
You shouldn't be looking at the header for the 1st match try
Excel Formula:
=INDEX(Master_List,MATCH($A11,Master_List[Name],0),MATCH(BK3&"",Master_List[#Headers],0))
 
Solution

Bravo2003

Active Member
Joined
Apr 1, 2010
Messages
271
Office Version
  1. 365
Platform
  1. Windows
You shouldn't be looking at the header for the 1st match try
Excel Formula:
=INDEX(Master_List,MATCH($A11,Master_List[Name],0),MATCH(BK3&"",Master_List[#Headers],0))
Brilliant, yes, that works..... BUT... next problem (as i'm new to working from table datasets... it isn't Absolute for the Name column if I drag across?

do I need to use [[Name]:[NAME]] ?

Actually, just tried that and it works...
=INDEX(Master_List,MATCH($A11,Master_List[[NAME]:[NAME]],0),MATCH(BK3&"",Master_List[#Headers],0))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,767
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,977
Messages
5,639,350
Members
417,082
Latest member
rbaltero

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
Top