Hello,
My first time, so apologies in advance for any forum etiquette mistakes.
Brief Background:
I have a table of data which is updated monthly, and I have to update another excel document manually. For this exercise I have created a small dumby document but it still has the same principles.
I have developed a formula which extracts a cell value and puts the value into another table, provided a given integer (serial number) is specified.
Formula:Please Login or Register to view this content.
Problem:
I will try my best to explain, however I ask please view the attached so it will make more sense.
Currently my INDEX MATCH MATCH is only extracting costs from line 1 to 6 (see table 1 below). This is an issue as when I seek information from line 8 "Vauxhall|5|Silver|1002yum|£8,465.25", my INDEX MATCH MATCH will never retrieve this as instead it will take line 2. This is due to the serial numbers being the same.
What I require is an initial formula that cross checks the three columns (car, door and colour), before it then looks for the serial number. My first obstacle is matching a partial string with a string in the car column. All I have managed so far is:
Formula:Please Login or Register to view this content.
First Table
Car Door Colour S/N Cost
Ford 5 Silver 1001ttcc £5,998.78
Ford 5 Silver 1002yun £3,454.56
Ford 5 Black 1003uyt £4,875.45
Ford 3 Black 1004ret £3,460.34
Ford 3 Silver 1005dft £8,973.34
Ford 3 Black 1006hgt £8,778.23
Vauxhall 5 Black 1001ttcc £3,785.45
Vauxhall 5 Silver 1002yun £8,465.25
Vauxhall 5 Black 1003uyt £1,549.35
Vauxhall 5 Silver 1004ret £1,237.56
Vauxhall 3 Silver 1005dft £7,895.64
Vauxhall 3 Silver 1006hgt £5,498.36
Second Table
Vauxhall 5 Door Silver
1002 3454.56 <- should be £8,465.25 (from line 8 in table 1)
1004 3460.34 <- should be £1,237.56 (from line 10 in table 1)
Thank you in advance, and I hope I have made this as clear as possible.
Harr
Bookmarks