Is an IF statement the best solution to display the desired data?

[ThomT25]

Hello,

I have limited Excel skills, and this is my first post. I volunteered to create a more user-friendly view of data for people not used to viewing spreadsheets. Here is some sample data:

I would create a 2nd worksheet called View Choice. When the person selects their choice, 1st, 2nd, 3rd, (in cell B6) I need to display the data for that choice:

The dataset would include choices 1-10. I must display about 25 fields for each choice consisting of short text, numbers, and dates. I only need to display one choice at a time.

Is using an IF statement in each field the most efficient way to do this...

=IF(B6="1st", INDEX($B$2, 1, 1), IF(B6="2nd", INDEX($C$2, 1, 1), IF(B6="3rd", INDEX($D$2, 1, 1), "Not Specified")))

...or is there a better solution?

Thank you in advance for any guidance you can provide.
Thom T.

[#2]

@ThomT25 Attached an example of how you could tackle this, using Data Validation, named ranges and an INDEX/MATCH function.

By using named ranges you don't have to worry about where the data sits in your workbook as Excel will find its location by its name.

See if you adopt these methods in your own workbook. Come back if you get stuck and provide a link to your file on Onedrive, Dropbox or similar, giving full access. Then it will be easier to help you further.

[#3]

@Riny_van_Eekelen 

THANK YOU - Your solution worked perfectly until I found out the worksheet structure differs from the sample I provided. I spent the morning trying to adapt your solution to the new structure, with no success  Here is the old vs. the new:

Could you update your formula for 1 cell (Location) to work with the new structure? I think once I see it, I can apply it to the other cells. Thank you - Thom

PS - I'm new to Office365 and can't figure out how to attach my Excel file from the cloud. 

[#4]

@ThomT25 

What you need to do that's to replace in Name Manager references for categories and choices (reference for categories becomes one for choices and the opposite). Formulae are the same.

and exchange 2nd and third parameters in INDEX formula.

[#5]

@Sergei Baklan 

Thank you for your help. When I changed the worksheet structure, the information that @Riny_van_Eekelen kindly provided did not work. I tried your suggestions with no success. Here are my latest results:

I have attached the file below. Any help you can provide is greatly appreciated. Thank you!
Thom

[#6]

@ThomT25 some of the named ranges were set-up incorrectly. See attached.

[#7]

@Riny_van_Eekelen 

My apologies. I should have included this screenshot in my last post. Here are the results I'm after: 

Are these results possible using the new table structure and the IF MATCH functions, or is there another way to achieve them? I've spent 8 hours re-defining the named ranges you provided, Googling, watching YouTube videos and even using ChatGPT but can't find a way to get the result I'm after.

Thank you again. 
Thom

[#8 · Accepted solution]

@ThomT25 Sorry, I went a bit too quick earlier. The MATCH arguments were in the wrong order to begin with. And obviously we can't refer to the same cell called 'selection' in all areas. The attached works now and I trust you can get it to work on your end.

[#9]

Thank you SO much Riny. I feel once I can dissect your solution, I'll finally understand how this works and be able to apply it. Have a great day! - Thom