Display 2 columns of data based on drop down selection

[RJB78]

Hello - Looking to display 2 columns of data from a source table based on a selection from a drop down in a separate tab. I have the drop down/data validation already (B1:D1) but need assistance in having the list(s) populate. For the data I'd like to show up, one column is static in terms of value/text (A), the second column displayed (B:D) will change based on the drop down selection. For example, if in the drop down I select 'best', Id like a Rating, price, margin in one column and 3, 3, 3 in the second column.

Maybe there is a better way to do this...

Source table

	Good	Better	Best
Rating	1	2	3
Price	1	2	3
Margin	1	2	3

 

[Peter_SSs]

I wasn't sure if you are looking for a formula to return the desired values or, say, some vba to physically hide the unwanted columns.
Here is a possible formula approach. Is that what you are after?

RJB78.xlsm
	A	B	C	D
1	Metric	Good	Better	Best
2	Rating	1	2	3
3	Price	1	2	3
4	Margin	1	2	3
Sheet1

RJB78.xlsm
	A	B	C
1	Best	Metric	Best
2		Rating	3
3		Price	3
4		Margin	3
Sheet2

Cell Formulas
Range	Formula
B1:C4	B1	=CHOOSECOLS(Sheet1!A1:D4,1,MATCH(A1,Sheet1!A1:D1,0))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=Sheet1!$B$1:$D$1

 

[RJB78]

I wasn't sure if you are looking for a formula to return the desired values or, say, some vba to physically hide the unwanted columns.
Here is a possible formula approach. Is that what you are after?

RJB78.xlsm
	A	B	C	D
1	Metric	Good	Better	Best
2	Rating	1	2	3
3	Price	1	2	3
4	Margin	1	2	3
Sheet1

RJB78.xlsm
	A	B	C
1	Best	Metric	Best
2		Rating	3
3		Price	3
4		Margin	3
Sheet2

Cell Formulas
Range	Formula
B1:C4	B1	=CHOOSECOLS(Sheet1!A1:D4,1,MATCH(A1,Sheet1!A1:D1,0))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=Sheet1!$B$1:$D$1

Thank you Peter. Formula is preferred. Using what you provided is very close the solution I'm looking for. I'm running into a small error where the values that are being returned are one column to the left of what I am looking for. Example- if I pick 'Better' it is returning 'Good' data. 'Best' gives me 'Better' Values

 

[Peter_SSs]

Sounds like your data in the first sheet are not in the same columns as mine. You can clearly see that mine occupies columns A, B, C and D in Sheet1. What columns is it for you?

For the future, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[RJB78]

Thank you Peter. I found in error in my version of the formula, adjusted for my data. I will look into the XL2BB

 

[Peter_SSs]

Thank you Peter. I found in error in my version of the formula, adjusted for my data.

You're welcome. Glad you sorted it out. Thanks for letting us know.