Here's my sample for easy reference
It illustrates a way to achieve the complex lookup that you seek:
http://www.savefile.com/files/2142559
Complex lookup on multiple var.xls

In "Enquiry", you'd have the variables selection table in A1:B6

Variables Select from droplist
Price List (Currency) US/USD
PN 345
Description A
License Type B
Geographic Float UK

To retrieve the hire rate based on the 5 variables selected in B2:B6
array-enter (ie press CTRL+SHIFT+ENTER to confirm the formula) in say, B10:
=OFFSET(INDIRECT("'"&B6&"'!A3:A100"),MATCH(1,(INDI RECT("'"&B6&"'!A3:A100")=B3)*(INDIRECT("'"&B6&"'!B 3:B100")=B4)*(INDIRECT("'"&B6&"'!C3:C100")=B5),0)-1,MATCH(B2,INDIRECT("'"&B6&"'!2:2"),0)-1)

This is what you'd have in each of the identically structured source
"country" sheets, eg in "USA":

PN Description License Type US/USD Euro Euro/GBP
123 A A 24 23 21
123 A B 26 98 55
345 A A 20 12 100
345 A B 70 17 68
etc

Ensure data is fully populated in each variable's column.
There should be no intervening blanks.

See Debra's page for ways to fill-in, if necessary:
http://www.contextures.com/xlDataEntry02.html
Excel -- Data Entry -- Fill Blank Cells


--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---