INDEX, multiple parameters help!

pauleconkle

New Member
Joined
Dec 14, 2016
Messages
8
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]K[/TD]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Type[/TD]
[TD]K[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]City[/TD]
[TD]1[/TD]
[TD]Afghanistan[/TD]
[TD]Kabul[/TD]
[TD]Post[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Kids[/TD]
[TD]2[/TD]
[TD]Afghanistan[/TD]
[TD]Kabul[/TD]
[TD]Away[/TD]
[TD]4000[/TD]
[TD]5000[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Location[/TD]
[TD][/TD]
[TD]Chile[/TD]
[TD]Santiago[/TD]
[TD]Post[/TD]
[TD]7000[/TD]
[TD]8000[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Chile[/TD]
[TD]Other[/TD]
[TD]Post[/TD]
[TD]10000[/TD]
[TD]11000[/TD]
[TD]12000[/TD]
[/TR]
</tbody>[/TABLE]
Morning everyone,
I'm working an INDEX(Parameters etc) formula and I've hit a bump. Basically, Someone will pick a country from a drop down in A1, a city from A2. They will then select 'Yes' or 'No' in A3. If they select 'No', my return in A5 will be N/A. If they select 'Yes' in A3, they have to select in column B grade 'K, 1, or 2'. Then in A4 they have to select 'Post' or 'Away'.

Now once that is all done, my search will pull the corresponding Country in column C and the city in Column D with the type (from cell A4). I then need my return in A5 to show the corresponding dollar value from F2:H5.

I am then working on cell A6 showing the next value so if A5 shows 1,000$ (from cell F2) A6 would show $2,000 (from cell G2).

I know this one seems crazy but any help would be appreciated...I think I've got a good start but I'm hitting a snag!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
what is your formula you currently have and what cells is it in
>>if its multiple parameters, it can be done multiple ways

i.e. INDEX(E1:E100,MATCH(B1&C1,F1:F100&G1:G100,0))
OR
INDEX([Target Values],MATCH([Area 1 Parameter]&[Area 2 Parameter],[Area 1 Lookup]&[Area 2 Lookup],0),MATCH([Area 3 Parameter]&[Area 4 Parameter]&[Area 5 Parameter],[Area 3 Lookup]&[Area 4 Lookup]&[Area 5 Lookup],0))
 
Last edited:
Upvote 0
what is your formula you currently have and what cells is it in

Hi- in A5 I have started with the following;

=INDEX(Parameters!F2:H5,MATCH(Parameters!A1&Parameters!A2&Parameters!A3,Parameters!C2:C5&Parameters!D2:D5&Parameters!E2:E5,0),MATCH(Parameters!B1,Parameters!F1:H1,0))

My tab is titled ‘Parameters’ fyi! I think I’m having trouble pulling in the Kids and how many…unless of course you see something else I’m missing! Thanks again
 
Upvote 0
no, you aren't actually typing anything in; when you have the formula activated / (when you are able to type in the formula box), hit CTRL+SHIFT+ENTER and that will make it an array
 
Upvote 0
Thank you. Got the array done correctly- but i still think i'm doing something wrong with my kid info. Previously before the kid info was added the formula seemed to be working but now I think I've screwed it up!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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