VLookup and If true tests

[darzan]

Hi,

I get sent a very detailed multi sheet spreadsheet monthly and need to pull the information. At present I have been using Vlookup and HlookUp to automatically extract the data I need. However the sheet I am being sent is changing each time and thus think there is a smarter way of doing this than how it has been set up.

I need to find the:
ROW: country with the specific variable then
COLUMN: draw from either volume or value and then draw either abc or def under the Volume/variable.

This all from a specific sheet.. yes i know!!!

The problem is coming in with the variables as VlookUp will draw the first occurrence of the country. Allowing me to easily draw variable x but not variable y (Note the country row is actually merged with the two variables in the next column).
Column is not a problem as I can do a simple count from VLookUp as that data stays constant.

I would like to find a way to tell the cell to look for the country then to check the cell next to it for a variable (note country column is rows merged not separate cells with two entries) and if not true to move down one row (still in the column next to to country (variable column)) and check if that is right (remembering that as a variable is sometimes broken down that it may need to move more that one row) while still ensuring that the country reference is kept.

See attachment for a short summary of what I get.

Thanks for the help!

[zbor]

You can use INDEX function where is:

INDEX(array,row_num,column_num)

and for row_num,column_num use MATCH function:

INDEX(array, MATCH(something), MATCH(something))

I still didn't get where you pulling data from but this would be approach to use.

[darzan]

Thanks,
Is there any function that will return an array instead of a row? (Match returns the first row where the value is found, even if that value is in multi column merged cells (and therefore an array)).

If i get the row array where the merged cell is (country a/country b) then i can use that array to find the row where the variable is located (variable x/variable y) and using that row then pull the data from the appropriate column...

An example of the data i am trying to pull would be:
ROW: country a, variable x,
COLUMN: Value, Year

Thus returning a single cell value.

[darzan]

Thought I'd found the solution with a combine (=A1&B1) then using that in MATCH to give row for the INDEX but as the cells are merged only the top cell of the merged group is being combined and the rest are just taking blank from the first column and the value from the second.


ARRRG!!! stupid merged cells!!!

Anyone have any ideas how to manipulate the merged cell to give an array?

[zbor]

Use a simple rule: Avoid merged cells in excel I don't have them in any of my tables

[darzan]

Use a simple rule: Avoid merged cells in excel I don't have them in any of my tables

I am being sent the sheet with their merged cells... might be easier to change theirs into what I need rather than changing mine...

any function to unmerge cells and replicate the data from the first cell into the other now unmerged cells?