Cross Check Columns for Index Match Match

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

And another one, based on the observation that the 4 digits at the start of the serial number are unique within any given brand. i.e. you would never have 2 Fords with a serial number staring 1001.

=SUMPRODUCT((B4:B18&" "&$C$4:$C$18&" "&LEFT($E$4:$E$18,4)&" "&$D$4:$D$18=SUBSTITUTE($J$7,"Door",J8))*$F$4:$F$18)

Or, if practical, a change to the format of table 2.

J7: 1002
K7: Vauxhall
L7: 5
M7: Silver
N7: =SUMIFS($F$4:$F$18,$B$4:$B$18,K8,$C$4:$C$18,L8,$D$4:$D$18,M8,$E$4:$E$18,J8&"*")

Hello,

Thank you for your suggestions. They all work in the dumby document I created, however when trying to replicate the formula where I actually need it, its not working

There are two reasons why I am struggling apply these formulae:
1. I cant specify a value in the formula i.e. "Door", instead I need it to be a variable. - Thanks Glenn and Jason, my fault for not mentioning in my OP.

2. I notice how we are trying to sum the headings in table one into one string and then confirm whether it equals the string heading in table 2. I didnt realise this method would be used as otherwise I would have mentioned the following: The heading for table 2 will be different i.e. "RVAUXHALL 5 DOOR SILVER, £m".
This is why I was trying a MATCH method, but I am self taught off the web so not sure if what I was trying below is achievable.

Formula:

Please Login or Register  to view this content.

Sorry for the aggravation, I realise now I should of tried to mirror my actual work moreso in the dumby doc.

Hello,

Thank you for your suggestions. They all work in the dumby document I created, however when trying to replicate the formula where I actually need it, its not working

There are two reasons why I am struggling apply these formulae:
1. I cant specify a value in the formula i.e. "Door", instead I need it to be a variable. - Thanks Glenn and Jason, my fault for not mentioning in my OP.

2. I notice how we are trying to sum the headings in table one into one string and then confirm whether it equals the string heading in table 2. I didnt realise this method would be used as otherwise I would have mentioned the following: The heading for table 2 will be different i.e. "RVAUXHALL 5 DOOR SILVER, £m".
This is why I was trying a MATCH method, but I am self taught off the web so not sure if what I was trying below is achievable.

Formula:

Please Login or Register  to view this content.

Sorry for the aggravation, I realise now I should of tried to mirror my actual work moreso in the dumby doc.

Hi Glenn,

Thanks for your prompt responses, sorry for the delay I had to create the attached and all of this code is very new to me. The attached is essentially what I am working with, excluding actual names and figures, so hopefully now you will be able to help more easily. Please do not feel all previous suggestions were to no avail, as they have certainly help me understand a lot more.

As you will see within the document, it is my intention to create an in-doc universal formula that can be dragged down, plus the total characters in a headings are changing accordingly. Thats why I am avoiding specific values and trying to stick to variables.

Yes, column A will always be just "AME" and "DEL".

Sorry, but I dont quite understand what you mean by universal rule.

Ah my apologies,

The "R" is for Resource, so it will only ever be RDEL.

Sheet 1 - > Sheet 2
AME - > AME
DEL - > RDEL

Hi Glenn,

Many Thanks for your perseverance with this, it works great! Just to incorporate it in my real docs now.

The formula will be covering about 200 rows

After a few issues I managed to finally implement your formula, again many thanks!

I will change this thread to solved, but before we leave this to the archives I was wondering if your method was the only approach? For instance, I was trying to Match a string [DEL] which had its characters within another string [RDEL PROGRAMME CASH, £m].

Haha okay, my apologies for making it so complicated as we went along, I thought my dumby doc would have simplified it.

I am very grateful as I now finally have a formula which will cut down a lot of time on transferring information, but in future, if I didnt want to add an extra column which substituted the old title with a new title (column N in your doc), is it possible to use just one formula in one cell to do as I desired? If it is, could you suggest possible articles or functions I can read into please.

Thanks,

Sorry Glenn,

When I deleted column N the formula no longer worked, so I decided to proceed and use your method anyway.

Aha, this is great! No problem at all, thank you for taking the time to help me with this.

Have a Happy New Year!

@Glenn,
Wait until you reach 40 then you'll really see acceleration!

Happy New Year

Hi All,

Sorry for continuing this solved thread, but I thought I would first, show the formula to all, and second, I added something new to the formula which I thought should be shared.

Formula:

Please Login or Register  to view this content.

It will return the last (bottom-most) text entry in the range I$10:I15: - Glenn, I hope you agree that this would be easier rather than having column K occupied?

Attached is the file with the updated formula in use.