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

Bravo2003

Active Member
Joined
Apr 1, 2010
Messages
278
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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))
 
Upvote 0
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))
 
Upvote 0
Is the value in BK3 numeric or text?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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: 11
Upvote 0
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))
 
Upvote 0
Solution
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))
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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