Hello my friends.
I hope you can help me here.
I have an excel with a matrix like the following
In this matrix you have the "origin city" as rows on the left, the "destination city" as columns , and the values.
Please Login or Register to view this content.
What I need to do is, given a origin city and a destination city, the macro must read the value.
Anyone can help me how to achieve this ?
Thank you in advance,
Bleeding_me
Last edited by bleeding_me; 02-16-2012 at 06:51 AM.
Assuming your table is in A1:F5 then one way would be a combination of INDEX and MATCH
=INDEX(B2:F5,MATCH(I3,A2:A5,0),MATCH(I4,B1:F1,0))
Where I3 contains Origin city and I4 destination city
Hello Andy,
thank you for your reply but I try what u say and I get #N/A...
This is what I put (I have excel 2007)
=INDEX(B2:F5;MATCH(I3;A2:A5;0);MATCH(I4;B1:F1;0))
Did you enter the 2 cities to lookup?
See attached,
Andy,
I'm sorry, it was my mistake in writing one of the cities...
This works like a charm...by the way, is there any way of "converting" this excel formula in a piece of VBA code ?
A bit behind the curve here, but the example was done so I'll post it.
This uses Dynamic Named Ranges with INDEX() & MATCH()
Expand the Matrix/Table as required.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
coded approach
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks