Lookup two factors in an array

[sjstewart1981]

Hi, I am trying to look up certain figures in an array and return the
values. This sounds like a simple Vlookup or Index and Match but what
I'm trying to do is create a template on one tab. This template will
relate to other tabs in the workbook which I will paste in from othere
workbooks. The problem is that not all the tables will be the same,
there are a maximum of 4 currencies which are my headers and within
this there are over 30 sectors. For the sake of my template I have all
4 currencies with all 30 sectors for each, but the reality for most of
the tables I copy into the other tabs (and hence want to look up) are
that they will have 2, 3 or 4 currencies with a differing number of
sectors for each. Here is an example where EUR is in Cell A1, Cash in
Cell A2, Industrials in Cell A3 etc...

Table 1:
Data Field 1 Date Field 2
EUR
CASH 2 1
INDUSTRIALS 3 1
UTILITIES 1 2
GBP
CASH 2 4
FORWARDS 4 7
QUASIS 3 8

Table 2:
Data Field 1 Date Field 2
USD
CASH 2 2
INDUSTRIALS 3 1
FORWARDS 3 2
SOVEREIGN 4 9
EUR
CASH 7 2
UTILITIES 5 2
SOVEREIGN 4 3
GBP
CASH 4 2
FUTURES 7 1


Etc…

Each table will continually change as I want to copy different tables
over the 3 tabs so the above example for Table 1 will change the next
time I run this query (if that makes sense?!). The problem I face is
how do I look up, for example, GBP Cash from Table 1 and GBP Cash from
Table 2 and add them together without the vlookup or whatever actually
returning the EUR Cash from Table 1 and USD Cash from Table 2 as they
are the first instances? Please let me know if you need any further
information.

Kind Regards,
Simon

 

[Bill Johnson]

Hi, I am trying to look up certain figures in an array and return the
values. This sounds like a simple Vlookup or Index and Match but what
I'm trying to do is create a template on one tab. This template will
relate to other tabs in the workbook which I will paste in from othere
workbooks. The problem is that not all the tables will be the same,
there are a maximum of 4 currencies which are my headers and within
this there are over 30 sectors. For the sake of my template I have all
4 currencies with all 30 sectors for each, but the reality for most of
the tables I copy into the other tabs (and hence want to look up) are
that they will have 2, 3 or 4 currencies with a differing number of
sectors for each. Here is an example where EUR is in Cell A1, Cash in
Cell A2, Industrials in Cell A3 etc...

Table 1:
Data Field 1 Date Field 2
EUR
CASH 2 1
INDUSTRIALS 3 1
UTILITIES 1 2
GBP
CASH 2 4
FORWARDS 4 7
QUASIS 3 8

Table 2:
Data Field 1 Date Field 2
USD
CASH 2 2
INDUSTRIALS 3 1
FORWARDS 3 2
SOVEREIGN 4 9
EUR
CASH 7 2
UTILITIES 5 2
SOVEREIGN 4 3
GBP
CASH 4 2
FUTURES 7 1


Etc…

Each table will continually change as I want to copy different tables
over the 3 tabs so the above example for Table 1 will change the next
time I run this query (if that makes sense?!). The problem I face is
how do I look up, for example, GBP Cash from Table 1 and GBP Cash from
Table 2 and add them together without the vlookup or whatever actually
returning the EUR Cash from Table 1 and USD Cash from Table 2 as they
are the first instances? Please let me know if you need any further
information.

Kind Regards,
Simon

I am pretty sure this is not the easiest answer, but you would need two
flags and a search loop.
The search loop would dig down though column 'A' waiting for the cell = the
first flag (your country code), when that was true and your keyword is true,
then you could copy the next two columns to the right into your location.

I bet you wanted the code to do this, instead of just how to do it. Sorry.