Find value in Multiple Table witn 2 condition

[Guest]

Hi Expert

Did anyone know how to search a value in the multiple table
ex

Table
Mexico USA Canada
Apple $1
Manggo $1
Grape $2

Hongkong China Thailan
Banana $33 $3
Orange $23 $25

Question, If I given a condition that I want a price for Banana Hongkong, how the formula it is
I try to use combination formula of index and match dpes only work in one table, but not in multiple table

Anyone with great expertise can help me????

 

[Frank Kabel]

Hi
do you have any chance to create only a single table for this?

 

[Guest]

Thanks for the reply

Actually the table that I given to you is just an example. And there is no way to join it into one table
Cause actualy the condition of each table is different.
In my project actually we are using part no and PO no.....so each table represent each customer
So the condition is different.

Pls advise. Thanks

 

[Frank Kabel]

Hi
you may explain the layout of your spreadsheet a little bit more:
- are these tables on separate sheets
- is the sheets named according to a customer name and do you enter
this customer name also as condition
- etc.

You may post some example data based on your real layout and use the
real lookup variables in this example

 

[Guest]

Yes, you re right....

The first worksheet contain a price list
The second workseet contain a daily report. So on the second workshet it may look like this

Items Country Qty Price Amoun
Condition1 condition2 5 (this is the formula base on condition1&2) qty*Price

Thks.

 

[Frank Kabel]

Hi
could you also explain the layout of your first sheet. e.g. how do you
separate the tables from each other?

 

[Guest]

OK

In first shee

Table 1: Customer ab
USA Canada France
Orange 2.3 2.2 2.
Apple 1.2 1.
Manggo 3.5 3.

Table 2: Customer bc
Hongkong Vietnam Malaysi
Banana 2.5 2.2 1.
Kiwi 3.
Grape 4.

Btw, fyi,,,,,,,the reason I separate into two is because the condition of country is exceed maximum column no in excel (which is IV column), thats why I need to divided into many column

Pls advise. Thanks.

 

[Frank Kabel]

Hi
if you like, email me your file and I'll set-up an example formula for
you.
email: frank[dot]kabel[at]freenet[dot]de