Hi folks
I'm not quite sure how or if this is possible but what I am wanting to do is lookup a number of column fields in another worksheet and if they ALL match exactly then return a value from that sheet. Any help would be massively appreciated! I have 3 sheets and at the far right of the 3rd sheet ('hun') I have 2 results columns: one to lookup to worksheet 1 ('arq') and the other to lookup to worksheet 2 ('jot').
Please see my attached spreadsheet. I have manually entered the results in columns U & V of 'hun' that should happen if the formula/macro works correctly. The columns in 'hun' that should be matched exactly in the other sheets are titled 'cgt', 'app', 'pinto', 'fen', 'iso', 'con', & 'res'.
sheet1:
Excel 2012
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2:
Excel 2012
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3:
Excel 2012
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
I have noticed that the 'jotFIG' figures (that are pasted from an external source) in col. Q of sheet 2 have four spaces to right so do not in fact have a number format, so I don't know if it is possible to also incorporate some kind of trim function that changes this to a number.
Thanks for any help!
I'm not quite sure how or if this is possible but what I am wanting to do is lookup a number of column fields in another worksheet and if they ALL match exactly then return a value from that sheet. Any help would be massively appreciated! I have 3 sheets and at the far right of the 3rd sheet ('hun') I have 2 results columns: one to lookup to worksheet 1 ('arq') and the other to lookup to worksheet 2 ('jot').
Please see my attached spreadsheet. I have manually entered the results in columns U & V of 'hun' that should happen if the formula/macro works correctly. The columns in 'hun' that should be matched exactly in the other sheets are titled 'cgt', 'app', 'pinto', 'fen', 'iso', 'con', & 'res'.
sheet1:
Excel 2012
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DATED: | 11/12/2015 | |||||||||||||||
2 | pro | B | cgt | app | pinto | fen | iso | con | res | J | K | L | M | N | O | arqFIG | Q |
3 | HK72115/(-281R) | misc | 0.40 | G | BBD2 | BG | RX | RX | N | misc | misc | misc | misc | misc | misc | 612 | misc |
4 | HK72685/(-152R) | misc | 0.40 | G | BD2 | BG | RX | BG | N | misc | misc | misc | misc | misc | misc | 606 | misc |
5 | HK79895/(-239R) | misc | 0.40 | F | BD2 | BG | RX | BG | N | misc | misc | misc | misc | misc | misc | 552 | misc |
6 | HK85111/-239R | misc | 0.41 | G | BD2 | Rx | RX | RX | N | misc | misc | misc | misc | misc | misc | 493 | misc |
7 | HK85332/(-239R) | misc | 0.43 | F | BBD2 | BG | BG | BG | N | misc | misc | misc | misc | misc | misc | 572 | misc |
8 | HK90113/(-239R) | misc | 0.45 | G | DI1 | G | RX | BG | N | misc | misc | misc | misc | misc | misc | 434 | misc |
9 | HK90115/(-219R) | misc | 0.45 | F | DI1 | ID | ID | ID | N | misc | misc | misc | misc | misc | misc | 434 | misc |
10 | HK90116/(-269R) | misc | 0.46 | G | DI1 | BG | BG | BG | N | misc | misc | misc | misc | misc | misc | 434 | misc |
11 | HK90202/(-298R) | misc | 0.47 | G | BBD1 | RX | RX | BG | N | misc | misc | misc | misc | misc | misc | 440 | misc |
12 | HK90204/(-219R) | misc | 0.49 | G | DI1 | BG | RX | RX | N | misc | misc | misc | misc | misc | misc | 434 | misc |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
arq
Sheet2:
Excel 2012
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DATED: | 11/12/2015 | ||||||||||||||||
2 | A | pro | cgt | D | app | pinto | G | fen | iso | con | res | L | M | N | O | P | jotFIG | R |
3 | misc | LKEK/72598/G | 0.40 | misc | F | BD2 | misc | RX | BG | RX | GNT | misc | misc | misc | misc | misc | £510.00 | misc |
4 | misc | LKEK/84116/G | 0.40 | misc | G | BD2 | misc | BG | RX | BG | GNT | misc | misc | misc | misc | misc | £490.00 | misc |
5 | misc | LKEK/89820/G | 0.42 | misc | G | BD2 | misc | RX | RX | RX | N | misc | misc | misc | misc | misc | £512.00 | misc |
6 | misc | LKEK/02948/G | 0.42 | misc | G | BD2 | misc | RX | RX | RX | N | misc | misc | misc | misc | misc | £512.00 | misc |
7 | misc | LKEK/98081/G | 0.42 | misc | F | DI1 | misc | RX | BG | BG | N | misc | misc | misc | misc | misc | £452.00 | misc |
8 | misc | LKEK/52737/G | 0.44 | misc | F | DI1 | misc | RX | BG | BG | N | misc | misc | misc | misc | misc | £452.00 | misc |
9 | misc | LKEK/16442/G | 0.45 | misc | F | DI1 | misc | ID | ID | ID | N | misc | misc | misc | misc | misc | £442.80 | misc |
10 | misc | LKEK/44604/G | 0.45 | misc | F | DI1 | misc | BG | ID | ID | N | misc | misc | misc | misc | misc | £524.80 | misc |
11 | misc | LKEK/88690/G | 0.45 | misc | F | DI1 | misc | RX | RX | RX | N | misc | misc | misc | misc | misc | £463.30 | misc |
12 | misc | LKEK/89409/G | 0.48 | misc | G | DI1 | misc | RX | RX | RX | N | misc | misc | misc | misc | misc | £466.20 | misc |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
jot
Sheet3:
Excel 2012
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DATED: | 08/12/2015 | rate | 0.657255 | ||||||||||||||||||
2 | pro | B | cgt | D | app | pinto | fen | iso | con | J | K | L | res | N | O | P | Q | R | S | hunFIG | arqFIG | jotFIG |
3 | VE4D62A2-19 | misc | 0.40 | misc | F | DI1 | BG | RX | BG | misc | misc | misc | N | misc | misc | misc | misc | $754 | misc | £496 | not found | not found |
4 | VE22B85Z13-198 | misc | 0.40 | misc | F | BD2 | BG | RX | BG | misc | misc | misc | N | misc | misc | misc | misc | $853 | misc | £561 | £552 | not found |
5 | VE14B70H18-662 | misc | 0.43 | misc | F | BBD1 | G | RX | BG | misc | misc | misc | F | misc | misc | misc | misc | $794 | misc | £522 | not found | not found |
6 | VE22B22Z20-44 | misc | 0.43 | misc | F | BBD2 | ID | RX | ID | misc | misc | misc | N | misc | misc | misc | misc | $928 | misc | £610 | not found | not found |
7 | VE1A07E1-539 | misc | 0.44 | misc | F | BD1 | ID | ID | ID | misc | misc | misc | N | misc | misc | misc | misc | $918 | misc | £604 | not found | not found |
8 | VE8Z33E15-5 | misc | 0.45 | misc | F | DI1 | BG | ID | ID | misc | misc | misc | N | misc | misc | misc | misc | $764 | misc | £502 | not found | £524.80 |
9 | VE8C53F15-431 | misc | 0.45 | misc | F | DI1 | ID | ID | ID | misc | misc | misc | N | misc | misc | misc | misc | $782 | misc | £514 | £434 | £442.80 |
10 | VE6H81B15-341 | misc | 0.47 | misc | G | BD2 | G | BG | BG | misc | misc | misc | M | misc | misc | misc | misc | $640 | misc | £421 | not found | not found |
11 | VE10Z04G11-232A | misc | 0.47 | misc | G | BBD1 | RX | RX | BG | misc | misc | misc | N | misc | misc | misc | misc | $916 | misc | £602 | £440 | not found |
12 | VE14B70H18-1201 | misc | 0.49 | misc | G | DI1 | RX | ID | ID | misc | misc | misc | N | misc | misc | misc | misc | $748 | misc | £492 | not found | not found |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
hun
I have noticed that the 'jotFIG' figures (that are pasted from an external source) in col. Q of sheet 2 have four spaces to right so do not in fact have a number format, so I don't know if it is possible to also incorporate some kind of trim function that changes this to a number.
Thanks for any help!