Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem - lookup formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem - lookup formula
Thanks a lot! -- laurafv |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Problem | Excel Worksheet Functions | |||
lookup problem | Excel Discussion (Misc queries) | |||
problem with v lookup | Excel Discussion (Misc queries) | |||
little problem with lookup i think | Excel Discussion (Misc queries) | |||
HELP -- probably a LOOKUP problem | Excel Discussion (Misc queries) |