Complicated

[Continental Translations]

In a sheet named "Confirmed" I have a spreadsheet which contains the
following:-

Column F (a list of languages)
Column G (a list of languages)
Column H (a fee per 1000 words)
Column I (a fee per 1000 words)

Now, I have set up a data validation series in a new sheet, so that when a
language is selected, say for example, Arabic which is in F1 of the sheet
"Confirmed", the languages listed next to it in G1 appears. If Arabic
appears in F1, F2 and F3, then the languages which are listed in G1,G2 and
G3 appear.

Now what I want to do, is for in a new sheet in cell A1, I want to be able
to type in an ammount, say for example 5000. Now, what I would like is for
Excel to have a look at what language is selected from my first dropdown
list and my second dropdown list. Then I would like it to cross-reference
this to my sheet called "Confirmed" and when it finds a match for those two
languages, I would like it to multiply the value in column H. If, however I
select "Yes" from another dropdown menu, then I would like it to multiply
the value in column I by the ammount typed in A1.

This is really hard to explain, and unless you have the sheets, it's hard
to imagine. If anybody is willing, I could email you the sheets to have a
look.

Thanks all

 

[Continental Translations]

I forgot to mention, if there are several of the same language combinations
in columns F and G, then I would like it to return the cheaper fee from
column H or I

 

[Frank Kabel]

Hi
give the following array formula a try (entered with CTRL+SIFT+ENTER):
=INDEX(OFFSET('confirmed'!$H$1:$H$100,0,--(X1="Yes")),MATCH(1,('confirm
ed'!$F$1:$F$100='sheet2'!$F$1)*('confirmed'!$G$1:$G$100='sheet2'!$G$1),
0))*A1

Assumptions:
- cell X1 contains your drop-down with 'Yes'
- sheet2 is the sheet with the data validation listboxes with F1 and G1
as the relevant fields