Ask Your Question

Retrieve a value from a row

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

Tales gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-08-28 01:08:44 +0200

Tales gravatar image

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 ) ) )
edit flag offensive delete link more

answered 2016-08-28 01:50:53 +0200

mark_t gravatar image

I couldn't follow what you wanted from your question, but from the answer you posted yourself I think you should use VLOOKUP.


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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 575 times

Last updated: Aug 28 '16