josvill2010
New Member
- Joined
- Jun 1, 2011
- Messages
- 40
- Office Version
- 365
HI Everybody,
There is a table that contains all information that I need to build another table.
At the moment I am searching manually and bringing the value from the other table across.
I was hoping to automate but the problem is that the search element has different values underneath in a different row and then in the next table.
I would like to bring this values across but I cannot remember how to ask the formula to bring the values below the Matching cell, and then from two columns to the right.
Attached is the example
There is a table that contains all information that I need to build another table.
At the moment I am searching manually and bringing the value from the other table across.
I was hoping to automate but the problem is that the search element has different values underneath in a different row and then in the next table.
I would like to bring this values across but I cannot remember how to ask the formula to bring the values below the Matching cell, and then from two columns to the right.
Attached is the example
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2 | A2 | =INDEX(test!D:D,MATCH(F1,test!C:C,0)) |
B2 | B2 | =INDEX(test!F:F,MATCH(F1,test!C:C,0)) |
C2 | C2 | =INDEX(test!E:E,MATCH(F1,test!C:C,0)) |
A3 | A3 | =INDEX(test!D:D,MATCH(F1,test!C:C,0)) |
B3 | B3 | =INDEX(test!F:F,MATCH(F1,test!C:C,0)) |
C3 | C3 | =INDEX(test!E:E,MATCH(F1,test!C:C,0)) |
A6 | A6 | =INDEX(test!D:D,MATCH(F6,test!C:C,0)) |
B6 | B6 | =INDEX(test!F:F,MATCH(F6,test!C:C,0)) |
C6 | C6 | =INDEX(test!E:E,MATCH(F6,test!C:C,0)) |
TestingExcelFormula.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | |||||||||
2 | L1 | Test1 | 2001-GL1 | ||||||
3 | GL | PO# | Supplier | Requester | PO Header Description | ||||
4 | 2001-GL1 | 100001 | Company1 | Joe | PO Great | ||||
5 | 2001-GL1 | 100002 | Company2 | Michael | PO Great 2 | ||||
6 | 2001-GL1 | 100003 | Company3 | David | PO Great 3 | ||||
7 | |||||||||
8 | L1 | Test1 | 2001-GL2 | ||||||
9 | GL | Supplier | Requester | PO Header Description | |||||
10 | 2001-GL2 | 100004 | Company4 | Richard | PO Great 4 | ||||
11 | |||||||||
test |