If Match array multiple cell by array

[CRogers]

I'm not sure that makes sense.

So I'm creating a walking challenge at work and I'm trying to make a tool for employee's to calculate number of steps by an activity.

Activity	Steps per Minute
Aerobic dancing class	127
Aerobic fitness class	181
Aerobics, low impact	125
Aerobics, step	153
Backpacking	181
Badminton, casual	131
Badminton, competitive	203
Ballet dancing	120				Activity	Minutes	Steps
Baseball	130				Baseball	25​
Basketball, game	145
Basketball, recreational	130

so Activity on the right is a drop down from the array in column A "Activity" I want to put in the minutes and it calculate the step for the activity.

I hope that makes some sense and someone can help.

Thank you in Advance for your help.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Activity	Steps per Minute
2	Aerobic dancing class	127
3	Aerobic fitness class	181
4	Aerobics, low impact	125
5	Aerobics, step	153
6	Backpacking	181
7	Badminton, casual	131
8	Badminton, competitive	203
9	Ballet dancing	120				Activity	Minutes	Steps
10	Baseball	130				Baseball	25	3250
11	Basketball, game	145
12	Basketball, recreational	130
Sheet6

Cell Formulas
Range	Formula
H10	H10	=XLOOKUP(F10,A2:A12,B2:B12)*G10

Cells with Data Validation
Cell	Allow	Criteria
F10	List	=$A$2:$A$12

 

[alansidman]

In power query, build two parameter queries as shown in the attached video tutorial.

 

[CRogers]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Activity	Steps per Minute
2	Aerobic dancing class	127
3	Aerobic fitness class	181
4	Aerobics, low impact	125
5	Aerobics, step	153
6	Backpacking	181
7	Badminton, casual	131
8	Badminton, competitive	203
9	Ballet dancing	120				Activity	Minutes	Steps
10	Baseball	130				Baseball	25	3250
11	Basketball, game	145
12	Basketball, recreational	130
Sheet6

Cell Formulas
Range	Formula
H10	H10	=XLOOKUP(F10,A2:A12,B2:B12)*G10

Cells with Data Validation
Cell	Allow	Criteria
F10	List	=$A$2:$A$12

Solved! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[alansidman]

Here's a link to the spreadsheet with the Power Query solution

Parameter Query.xlsx | Powered by Box

app.box.com