Index and Match issues

Dear all

I have a query on my formular as attached

it works for the 1st row only

When I select the Day Tour, Adult, it prompts "N/A"

Appreciate your help


Guests 10 Child
Unit
Day Tour Aduit #N/A

Eric

Ankur

Thank a lot , possible if you would advise me

what formular, if i selected the Adult and the Child column will get the data ..

Thanks

Try

=INDEX($B$2:$E$5,MATCH($G6,$A$2:$A$5,0),MATCH(O$3,$B$1:$E$1,0))

Sorry for my English

in the attached file,

Unit Child
Day Tour Adult 200 xxxx (185 )

if i select the Ädult in the cell, the Child column can get the figure "185

Eric

John

i want the If H6 is Ädult, then J6 will get the figure from D3:D4
Only of Adult is selected

Eric

Dear all

I have apply the Match & index function again. But i think it should not be the case

Eric

This ..

=IF(H6="Adult",INDEX($B$2:$E$5,MATCH($G6,$A$2:$A$5,0),MATCH(O$3,$B$1:$E$1,0)),0)

As Ankur has requested please post a file with more comprehensive data and show expected results. You have column headings 10, 15 etc which at the moment mean nothing to us.

See attached

See my post #11 re the original file..


You have changed the data but still refer to H6 in your comment!

John

I have a new question, please kindly advise what wrong with me formula.
It does not work

Eric

John

When i select the choice, it does not work , and prompt "REF""

Eric

Try

=INDEX(rate,MATCH($D11,Services,0),MATCH($E11,Items,0))


You had ROW/COLUMN MATCH wrong way round

John

I made the changed and it seems works

Appreciated your comment.. Any where i can improved

Eric

Looks good to me.

One possible change: make the US$ exchange rate a variable rather than the fixed 7.7 currently.

If it varies (as I am sure it does) over time, consider a LOOKUP table with two columns: Date Effective and Exchange Rate (in ascending date order).

For LOOKUP purposes, "Date Effective" could be "date of booking".

Dear John,

Thanks a lot and i have further amended the formular,

for the exchange rate, as HKD is pegged with US, so the rate is secured

the next step would be how to make a "input form"to make user more convenience ...

You have any comments/advice ?

Eric

You could have a VBA "userform" solution or a simple form in Excel using (as you already have) drop-down lists which reduces the likelihood of input error.

You need to consider what data is on the form and what checking needs to be incorporated.

The VBA option is the more "sophiscated" but requires more work to develop. If you decide to go down this route it is probably worth putting a thread on the "VBA Macro" forum. However you need to specify very clearly and in detail what is required.