Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup two factors in an array
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup two factors in an array
" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Lookup | New Users to Excel | |||
Lookup from an array | Excel Discussion (Misc queries) | |||
Lookup array | Excel Worksheet Functions | |||
Lookup, Max, Array | Excel Discussion (Misc queries) | |||
Lookup "greater than or equal to" in lookup array | New Users to Excel |