Find value in 2D array and return value in adjacent cell

0
votes
**Sheet 1**

ColumnA    B    C      D        E      F       G      H
------------------------------------------------------------
EURUSD  1.2765  1  ACCOUNT624   2  account125  1  account834 
EURCAD  1.01    2  Account49    3  account45   2  account67  
EURGBP  0.78    2  Account777   1  account45   2  account678 



**Sheet 2**
ColumnA    B         C             D 
---------------------------------------
EURUSD 1.2765     Account 624      ?
EURUSD 1.2765     Account 125
EURUSD 1.2765     Account 834
EURCAD 1.01       Account49
EURCAD 1.01       Account45

In Sheet 1 above each row shows a currency trade and what quantity goes to each account.In Sheet 2 each row shows 1 account only. I would like to populate columnd D in sheet 2 with the quantites from sheet 1.

Breaking it up into steps, i would like to:

  1. Find the price in Sheet2!B1 in sheet1
  2. On the same row in sheet1, find the cell containing the same account as Sheet2!C1
  3. Return value in cell to the left of cell with matching account

I have used index/match before but I can't get it to work for 2 dimensional arrays. Can anyone help with a formula? Thanks in advance!

1
excelexcel-formulaexcel-2010

1 Answers

2
votes

It's not pretty, but using what you requested - to find the match based upon price in column B (I would say your safer bet would be to use the Currency conversion "EURUSD", for example, since what if 2 currencies have the same ocnversion rate??), paste this formula in cell D1 on your second sheet:

=OFFSET(Sheet1!$B$1,MATCH(B1,Sheet1!$B$1:$B$3,0)-1,MATCH(C1,OFFSET(Sheet1!$B$1,MATCH(B1,Sheet1!$B$1:$B$3,0)-1,0,1,10),0))

You can then drag it down / change ranges as needed.

(PS - I'm also assuming you made a mistake on sheet2 and that the account numbers will be typed the exact same in both sheets)