If function

[BPM Aditya]

Sheet1
A B C
1 City Fruit Price
2 Chennai Apple 100
3 Kolkata Mango 150
4 Mumbai Grapes 200
5 NewDelhi Papayya 250
6 Mumbai Apple 100
7 Kolkata Mango 200
8 NewDelhi Papayya 300
9 Chennai Grapes 400
10 Kolkata Apple 500
11 NewDelhi Grapes 400
12 Chennai Papayya 200
13 Mumbai Mango 300
14 Kolkata Mango 100
15 Mumbai Grapes 100

Sheet 2

A B C D
1 Mango Grapes Apple
2 Kolkatta
3 Chennai
4 NewDelhi
5 mumabi

Now I want to create a formula in sheet 2 so that the correspondant data in sheet1 is retrieved. The above 2 sheets are as example. The original sheets which I am having are huge in size, having almost 10600 rows and 100 colums. How can I retrieve the data by using formula. I have a little idea about the formula but dont know completely to finish it. Can any body help me?

The formula in cell B2 in sheet2, should be like this.. if 'Shee2'!A2 = 'Shee1'!A1:A15 and if 'Shee2'!B1 = 'Shee1'!B2:B15, the correspondant value in shee1 should be appear in B2 in sheet2.

I have tried IF, LOOKUP, VLOOKUP but it did not worked, because data length in shee1 and sheet2 are not same, cannot be aranged in ascending order. Moreover look up function cannot combine more than 2 orguments.

 

[p45cal]

Taking Mangos in Kolkata:
1. Are there two cities? One called Kolkata, the other Kolkatta?
Make sure spelling corresponds; not only spelling, but make sure there aren't extra spaces before or after the name.
2. In sheet 1 there are 3 rows with Kolkat(t)a and Mangos. Each has a different price. Which one?

 

[Akashwani]

Hi,

If this is your sample data on Sheet1?...

Excel Workbook
	A	B	C
1	City	Fruit	Price
2	Chennai	Apple	100
3	Kolkata	Mango	150
4	Mumbai	Grapes	200
5	NewDelhi	Papayya	250
6	Mumbai	Apple	100
7	Kolkata	Mango	200
8	NewDelhi	Papayya	300
9	Chennai	Grapes	400
10	Kolkata	Apple	500
11	NewDelhi	Grapes	400
12	Chennai	Papayya	200
13	Mumbai	Mango	300
14	Kolkata	Mango	100
15	Mumbai	Grapes	100
Sheet1

What do you want on Sheet2?

Ak