Retrieve a value from a row

I have 2 sheets in 1 document. Sheet1 contains a lot of cities, each one has descriptive columns of the city, including City Name, Country, Population, etc. Sheet2 has a list of countries, and several descriptive columns of each country, including Population and GDP. In the first sheet In column H, I want to calculate the % of population of each city, relative to the country. The way I would get this value is the following: 1. Get the population of the city located in column C 2. Find the row in Sheet 2 where Sheet1, column B equals Sheet2, column A 2. Get the value of Sheet2, column B for the row obtained in previous step 3. Divide the value of Sheet1, column C between the value obtained in previous step

Any ideas about the formula?

edit retag close merge delete

Sort by » oldest newest most voted

After some research I got this:

This formula gets the value of the row that contains the string that matches $B3 or B3: MATCH($B3, Sheet2.$A$1:$A$195 )


This one will just create a string that joins "Sheet2.$B" + A number (The row that contains the country) CONCATENATE( "Sheet2.$B", MATCH( $B3, Sheet2.$A$1:$A$195 ) )  INDIRECT will get me the value of a cell in a string INDIRECT( CONCATENATE( "Sheet2.$B", MATCH( $B3, Sheet2.$A$1:$A$195 ) ) )  The complete formula in H column will be: =C3/INDIRECT( CONCATENATE( "Sheet2.$B", MATCH( $B3, Sheet2.$A$1:$A$195 ) ) )  more I couldn't follow what you wanted from your question, but from the answer you posted yourself I think you should use VLOOKUP. =C3/VLOOKUP($B3,Sheet2.$A$1:$B$195,2,0)


I didn't test this as I don't have sample data.

more

Stats

Asked: 2016-08-28 00:08:01 +0200

Seen: 575 times

Last updated: Aug 28 '16