index and match across multiple worksheets

i have 8 worksheets with towns and citys and the distances between each one, i can index and match on one worksheet but cannot index and match across more than that,
hopefully there is a way of doing it

many thanks

Originally Posted by pertenax

i have 8 worksheets with towns and citys and the distances between each one, i can index and match on one worksheet but cannot index and match across more than that,
hopefully there is a way of doing it

many thanks

List your worksheets in column C

Please Login or Register  to view this content.

If the formula does not work please post a sample workbook with the expected results.


Hope this helps

many thanks for the reply vane0326, my original post was a bit misleading index and match across multiple worksheets but against two criteria, i have attached a sample workbook hopefully you can help me out

many thanks

Originally Posted by pertenax

many thanks for the reply vane0326, my original post was a bit misleading index and match across multiple worksheets but against two criteria, i have attached a sample workbook hopefully you can help me out

many thanks

Bit confuse, What is the result suppose to be in worksheet Data in cell D2?

hi vane0326

sorry i was in a little bit of a hurry, the result should be "mileage2 cell O3" 268 on the data sheet in cell a2 should be "city3".

hopefully this explains it a bit better, sorry for the confusion

many thanks

pertenax



Look at the attachment below and add the necessary define name range formulas.

Example,

=LOOKUP(10^307,CHOOSE({1,2,3,4,5,6},0,Table,Table2,Table3,Table4,Table,5))


Hope this helps!

Many thanks to both of you they both work brilliantly, thanks very much for your insightful knowledge.

Thank you.

bump for more info :D

hi there, how about matching the index over different spreadsheet? is that possible?

Thanks

Originally Posted by martindwilson

try in d2 data
=INDEX(MILEAGE!1:342,MATCH(A2,MILEAGE!A1:A342,0),MATCH(B2,MILEAGE!1:1,0))




and in d3 data
=INDEX(MILEAGE2!A1:O342,MATCH(A2,MILEAGE2!A1:A342,0),MATCH(B2,MILEAGE2!A1:O1,0))

another thing to consider is are there really more than 256 cities? if not change your references to cities in the row and towns in the column,then you could put all on one sheet

Hi there, how about using Index in different spreadsheet, it's possible? what will the formula look like

Thanks

Vane,

Could you please give a brief description about the tables (Table1, table2...) you used in the attachment?

is it a simple excel table or any vba user defined functions?

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.