INDEX, multiple parameters help!

[pauleconkle]

[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!

 

[bsquad]

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))

 

[pauleconkle]

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

 

[bsquad]

these are typically an array >> {}, have you tried CTRL+SHIFT+ENTERing it?

 

[pauleconkle]

I totally forgot about arrays! Would I put { after my equal sign and then at the end of the formula? Thanks

 

[bsquad]

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

 

[pauleconkle]

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!

 

[bsquad]

np, thank you for the feedback, glad to have helped.