Problem - lookup formula

[laurafv]

I need urgent help with this I've been trying to find a formula fo
hours with no luck!!

I need to perform lookup using two rows as a criteria...


I have two problems:


Problem 1.- I have a file containing the data I want to lookup fo
(i.e. ratings):

philippines
2007 rating 1
2005 rating 2
2000 rating 3
1989 rating 4
...


brazil
2000 rating 5
1995 rating 6

usa
1970 rating 7
1985 rating 8

etc
(180 countries)

and i have to copy the ratings in a file that looks like this:

argentina 1980 rating
argentina 1981 rating
argentina 1982 rating
(...)
argentina 2007 rating
(...)
zambia 1980
(...)
zambia 2007

(180 countries)

any ideas on how to solve this?

Problem 2 .- I have other ratings in the following format:

country 1 year 1 rating 1
country 1 year 2 rating2
etc

that need to be looked up and imported in a sheet in the followin
format:

argentina 1980 rating
argentina 1981 rating
argentina 1982 rating
argentina 2007 rating
....
zambia 1980
etc
zambia 2007

Thanks a lot!


Laura

 

[Bernie Deitrick]

Laura,

Start by transforming your Ratings file - by entering a formula in another
column where you combine the values.

Say that Philippines is in cell A2, and your other values are all in cells
in column A, with a single blank cell between countries. Make sure that A1
is blank, and then in D2, enter this formula

=IF(A1="",A2,D1)

and in E2, enter this formula:

=IF(AND(A2<>D2,A2<>""),A2,"")

and copy both of those down the column to match column A.

Then copy columns D and E and paste special values, then sort D and E based
on column E, and delete the cells in D and E where column E is empty, then
resort based on Column D. Then use Data / Text to columns on column E, with
space as the delimiter. Then you'll have a database of values, and you can
either combine the country and year to get a key value to use with VLOOKUPs,
or use SUMPRODUCT formulas to extract the data.

HTH,
Bernie
MS Excel MVP

 

[laurafv]

Thanks a lot