Problem - lookup formula
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" wrote in message
...
I need urgent help with this ;) I've been trying to find a formula for
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 for
(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 following
format:
argentina 1980 rating
argentina 1981 rating
argentina 1982 rating
argentina 2007 rating
...
zambia 1980
etc
zambia 2007
Thanks a lot!
Laura.
--
laurafv
|