Solution to return a single value from a multiple condition if formula

[akuk]

Hi,

Could anyone please suggest a solution to return a single value from a multiple condition IF formula OR any other way (macro) for below problem?

I have to select a single criteria in my main working file out of many criteria (Z1, Z2, so on) designed in a separate sheet (refer below table) to select and accordingly multiple column (A,B,C) with different % fill in to the main file.

eg. If i select Z1 in main file, I get return value in A column as 5%, B column as 10% and so on.
If I select Z2 in main file, I get return value of A column a 8%, B column as 16% and so on.

Criteria	A	B	C
Z1	5.00%	10.00%	20.00%
Z2	8.00%	16.00%	24.00%

Please advise.

 

[Joe4]

Welcome to the Board!

Are you using Excel 365 (you should update your profile to show which version of Excel you are using).
If so, you may be able to use the new FILTER function.
See: FILTER function

If not, you may be able use a series of VLOOKUP, XLOOKUP, or INDEX/MATCH functions:
See: MS Excel: How to use the VLOOKUP Function (WS)

 

[akuk]

Welcome to the Board!

Are you using Excel 365 (you should update your profile to show which version of Excel you are using).
If so, you may be able to use the new FILTER function.
See: FILTER function

If not, you may be able use a series of VLOOKUP, XLOOKUP, or INDEX/MATCH functions:
See: MS Excel: How to use the VLOOKUP Function (WS)

Thank you.
I am using excel 365

I am just beginner so appreciate if you/anyone else can write a formula of VBA for below problem:
Criteria in sheet1 and Result are required in sheet2.
eg. If I select/write Z2 in sheet2 then it automatically populate the result in A, B, C column of that row in sheet2.

Criteria (Sheet1)	A	B	C
Z1	5.00%	10.00%	20.00%
Z2	8.00%	16.00%	24.00%
Z3	10.00%	18.00%	26.00%
Result (Sheet 2)	A	B	C
Z2	8.00%	16.00%	24.00%

Thank you in advance.

 

[Joe4]

No VBA needed.

If this is what you have on Sheet1:

and you enter "Z2" in cell A2 on Sheet2, you can get the other three columns populated by entering this formula in cell B2:

=FILTER(Sheet1!B2:D4,Sheet1!A2:A4=Sheet2!A2)

So the result looks like this:

 

[akuk]

No VBA needed.

If this is what you have on Sheet1:

View attachment 76911

and you enter "Z2" in cell A2 on Sheet2, you can get the other three columns populated by entering this formula in cell B2:

=FILTER(Sheet1!B2:D4,Sheet1!A2:A4=Sheet2!A2)

So the result looks like this:
View attachment 76912

 

[akuk]

Thank you.. much appreciate the solution.

 

[Joe4]

You are welcome.
Glad I was able to help!