Pull Data from one sheet to another based on 4 conditions.

[thorton70]

Hi guys,

I am moving along in my masters project and I am having a problem organizing some data I got dumped with.

What I have is a 196 x 196 matrix of the worlds countries and the distance between each one. So each Row has a title header of a country and each column has a country and the cells between the two are the distance of said countries.

On another sheet I have about 10 000 different combinations of dates/years/months ect that I need to populate in order for Stata to work properly.

Rather than manually enter thse in one by one or copy paste what I would like to do is write a code that would do something like this.

If on sheet 2 (the combination sheet) A3 = Afghanistain and C3 = China than F3 = The China/Afghanistain intersection (In this case AN3 on the Distance Sheet)

I am having problems because I can't see to figure out a way to have a program lookup the intersection and give me the China/Afghanistan number based on all the conditions. I am assuming I need to use a matrix lookup of some sort. This way when I do some like Canada/Denmark on the Combination sheet, it will automatically look up and populate the distance from the distance sheet (That being AZ33).

What steps would I have to take to work towards fixing this?

[Speshul]

Something like this? (Adjust as needed!)

=VLOOKUP(I6,$A$1:$F$6,INDEX($A$1:$F$1,MATCH(J6,$A$1:$F$1))+1,FALSE)

Where $A$1:$F$6 is your table of 196x196
$A$1:$F$1 is your Top row of countries
I6 and J6 contain the two countries you need to find distance between

[MarvinP]

Hi thorton and welcome to the forum,

See if the attached example is what you need. I didn't understand the date part of your question. You, most likely, will need to supply a sample workbook so we can see the data layout to do a better job with your problem.

I made a mistake in the attached and the formula should be:

=OFFSET(A1,MATCH(D15,$A$1:$A$11,0)-1,MATCH(D16,$A$1:$K$1,0)-1)

in Cell F15. I needed to subtract 1 from the match functions to get it correct.

[Speshul]

in F3 on the Combination sheet (Assuming the Distance sheet is called "Distance")

Please Login or Register  to view this content.

[thorton70]

Thank you guys, I will try these suggestions and if not I will make a smaller version of the table and upload, as it is right now it is pretty huge. You guys might save me hours and hours of frustration

[Speshul]

Amendment to my formula - looks like it doesn't need the Index

Please Login or Register  to view this content.

[thorton70]

I managed to get your code to work Speshul with some slight modividation. The matrix was A1:GV201 (my mistake)

Please Login or Register  to view this content.

the only odd thing was it only worked if I did the full matrix for the first lookup (A1:GV201) and only the header rows for the match (A1:GV1). Any idea why?

Thanks again guys.

[Speshul]

I managed to get your code to work Speshul with some slight modividation. The matrix was A1:GV201 (my mistake)

Please Login or Register  to view this content.

the only odd thing was it only worked if I did the full matrix for the first lookup (A1:GV201) and only the header rows for the match (A1:GV1). Any idea why?

Thanks again guys.

Yeah that'll be my bad. I obviously tested on a smaller scale, and typed in your references as I interpreted them. I guess I accidentally put 196 on that second table, should have only been the header.

I will assume you know how VLOOKUP works, the way this formula works with the MATCH is:

MATCH will find a value in a list, and tell you what position it is in. So in this case, you are finding Country B in a list that is the top horizontal Row (The VLOOKUP takes care of the First column search)

So the VLOOKUP's col_index_num value comes from the MATCH, which tells the VLOOKUP what column it found it's match number...if that makes sense

Reason it wasn't working with the full table is the MATCH formula likes a one-column or one-row list

Glad it works for you!

[thorton70]

It does, I have not, until now, used both Vlookup and Match, though it appears as if it would have made my life easier the last few years. This will be coming in handy during the next few months, thanks again, you saved me a lot of time.

[Speshul]

Don't forget the mark the thread as [SOLVED] (above the original post, there is a Thread Tools menu)

And welcome to the forums!

[thorton70]

Here is a "small" sample of the excel doc I had been using. Distances.xlsx