Formula finding values in minitables

1
votes

I need to extract values from WORKBOOK1 with unique set of tables to WORKBOOK2 where my results are.

My results workbook contains vlookup value (name)

http://i.stack.imgur.com/9FfrS.jpg - result table WORKBOOK2

In the column AMOUNT I need to put my formula (that can be dragged down).

This is how the data workbook looks:

http://i.stack.imgur.com/n3f3I.jpg - data table WORKBOOK1

So I need to first find the name (country in this case), then somehow ask Excel to tell me the value to the right and below in the same row where BALANCE is. I tried to do that with VLOOKUP, OFFSET, MATCH but I failed.

2
excelexcel-formula
Paste Link? Did you mean link directly to the cell? - mm90
There is an example here of a 2d lookup which could be adapted to your question exceluser.com/blog/1043/… - Tom Sharpe
I tried but it seems to be a different problem with different outcome. I couldnt adapt it - mm90
Yes, data workbook is not under my control, i can only read it but not edit it, "SPAIN" is guaranted to be found only once and yes, SPAIN will remain in the same position relative to the BALANCE and the numeric value - mm90
I want to avoid using VBA. SPAIN CAN be found with Find from the Ribbon. - mm90

2 Answers

0
votes

So using the reference here to do a 2-d lookup, and assuming that the offset between the name of the country and the amount is always 7 rows and 2 columns, and that each country only occurs once, the formula you need would be

=INDEX([workbook1.xlsx]Sheet1!$A$1:$K$20,SUMPRODUCT(([workbook1.xlsx]Sheet1!$A$1:$K$20=B4)*ROW([workbook1.xlsx]Sheet1!$A$1:$K$20))+7,SUMPRODUCT(([workbook1.xlsx]Sheet1!$A$1:$K$20=B4)*COLUMN([workbook1.xlsx]Sheet1!$A$1:$K$20))+2)

and it would fill in the amounts like this

enter image description here

0
votes

First Time Asn : If you have a NAME, its easy.I po
You can create Excel names that refer to cells, a range of cells, a constant value, or a formula.1

1.Name categories you want to calculate.

2.Use name in formula.

==================================================================================
Second Time Asn : If you can't edit that workbook.I still thinking about direct link and po
The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.2

Spain BALANCE:

Set A1 as ''C:\Users\TMuser\Desktop[WORKBOOK1.xlsx]Sheet1'!D10
indirect(A1); 'WORKBOOK1.xlsx must open

OR

='C:\Users\TMuser\Desktop\WorkBookSrc.xlsx'!D10
'WORKBOOK1.xlsx Not necessarily open

==================================================================================
Third Time Asn : found INDEX + SUMPRODUCT + ROW + COLUMN SOLVED. I realize this question, try another way to solve this

INDEX + MATCH
1.Use MATCH get row and column num
2.Use INDEX get context of that row and col of an area(OFFSET only get context of that row and col)

1.first search the workbook for SPAIN
2.return the value that is 3 columns to the right and 8 rows below

My Asn:
=INDEX((workbook1.xlsx!Spain,workbook1.xlsx!Canada),8,3,1 or 2)