Index / Match Error

[tress]

Hi,

I am needing some help with an Index / Match formula that keeps showing an error part way though but I don't know why. I work mainly with SQL Server so Excel functions are not something I need to do much of on a day to day basis.

In columns A to D are my reference columns and in G to I are my updating columns.

What I am doing is looking up the value from G column in column A, checking that the Qty matches and then putting the value in column I. The exception to this is, if the Qty in column H is 0 then I is automatically 0. This all works correctly in the attached file but the issue is for example.

I create a new reference record in A to D and then the record directly opposite shows a #N/A in the I column where previously it was fine. If I then go on to create a record that needs to look at the reference table it comes up correctly but I still have #N/A in the above record.

In essence with the exception of where the Qty is 0, I want to match what is in column G to what is in A and where D = H and then put the value from C in to I.

I have noticed though if I have the value in A in G but perhaps it has letters or numbers either side it also fails, I am wanting to check that A appears in G whether there are any other letters around it or not e.g. A Column = WS124NS G Column = 3WS124NSRW this is still a match as WS124NS can still be seen in the G column.

If I can offer any more information please do ask.

 

[vletm]

tress
My opinion is that Excel try to calculate just as You've written!
You wrote many thing which You've tested and so on ... which would have give something ... hmm?
I tried to find out what are You really looking for?
Could it be something like this?
It would be written other ways, but I tried to use Your named Index/Match.

 

[tress]

Hi Veltm,

Thanks for the reply, its much appreciated. My issues are still there as when I add new records in, it invalidates records that it has previously found and put in the correct Sal value for?

Thanks Phil

 

[vletm]

tress
Where do You add something?
If more rows then those have to take care with formulas!
=IFERROR(IF(H2=INDEX(D:D,MATCH(G2,A:A,0)),INDEX(C:C,MATCH(G2,A:A,0)),"Qty No Match"),"UnKnown ID")
Give clear samples of Your challenge.

 

[tress]

Hi vletm,

Sorry for my late reply but I have been off work for a few days, I have taken the example that you added your code to and tried to explain further.

Where the text is red, as you can see the ID value in column G matches an ID value in column A but the Qty is 0, whenever the Qty is 0, Sal must be set to 0 no matter what the ID value is.

Where the text is blue, your formula is currently saying Unknown ID, this should be returning the value I have shown in my expected result column. If we look at the value 456A in column G, this can be referenced to to column A as there is a 456 in there. I have tested and this is because in the formula where the MatchType is set, it is currently set to 0 to get an exact match. To make this work the way I intend it to I would need to set this to 1 to allow for the extra letter in the ID field. If I do this, the error is no longer "UnKnown ID" but "Qty No Match", I don't know why this error is showing as the Qty is exact?

Thanks again for the help.

 

[Nebu]

Hi:

May be this formula?

=IFERROR(IF(H2=LOOKUP(2^15,SEARCH($A$1:$A$10,G2),$D$1:$D$10),LOOKUP(2^15,SEARCH($A$1:$A$10,G2),$C$1:$C$10),"Qty No Match"),"UnKnown ID")

Thanks

 

[vletm]

tress
Red 0 - if there is no 0 in D-column ... how did You get 0?
Blues - eg 456 & 456A ... how did You get something else than UnKnown ID'?
for me those are different IDs ... it's same as You would write that 1 & 111 or 1 & 123A would be same!
If You would like to compare only 1st letter then okay - or 2 letters from begin ... but then case would be different.

You wanted to use INDEX/MATCH!

You can 'play' Yourself with Match's 'match-type' ( the last parameter) if You have time - I don't have:

• If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The argument lookup_arraymust be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
• If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. The argument lookup_array can be in any order.
• If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The argument lookup_arraymust be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
• If match_type is omitted, it is assumed to be 1.

 

[tress]

Hi vletm,

Thanks again for the reply.

Being no expert in Excel I chose Index/Match as I thought this best suited my requirement.

Red 0 - My apologies, I was wanting to use the same logic as I would use in SQL, with various case statements so if an ID match was found but the Qty in column H was 0 then Sal would be 0

Blue - Again my apologies I was hoping something like PATINDEX "SQL" existed in Excel, though I appreciate in your example making this work would be quite complex in the case of 1 & 111.

Thanks for the help and I will work with what you have helped me with and make the changes that I need.

Regards.

 

[tress]

Hi:

May be this formula?

=IFERROR(IF(H2=LOOKUP(2^15,SEARCH($A$1:$A$10,G2),$D$1:$D$10),LOOKUP(2^15,SEARCH($A$1:$A$10,G2),$C$1:$C$10),"Qty No Match"),"UnKnown ID")

Thanks

Hi Nebu,

That works exactly as I wanted though badly explained on my part, can you tell me what the "LOOKUP(2^15" is doing though?

Thanks P

 

[vletm]

tress
... still wondering why to use 'not exact match' (not me!) ...
If You have 456 and You'll try to find 'something' ... okay?
If there are 456a, 456b, 456c ... which one would be Your needed correct 'almost correct match'?
456a would mean +1000, 456b would mean -10000 & 456c mean 0 (mean ~ the effect of You 'find').
The result of 'not exact match' would be ... whatever! ... of course, it could be correct too.
hmm?

 

[tress]

Sorry for having to ask for further assistance but I am not sure what is happening here. I have applied the code to my working sheets and in a number of cases I am getting the wrong result. In my uploaded example on row 12 columns G and H I am getting the Qty Issue warning, the reason for this is because I have added a second record in my reference table with an ID of 5G12VA though they have 2 different Qty and Sal Values (Row 10 & 11). If I take the data out from row 11 then Qty Issue shows the right Qty to the right. Does anyone know the reason for this?

 

[vletm]

tress
Would it be as I tried to write above
... How would even Excel know/guess to be 99% sure, which one is correct, if not exact match?

 

[Nebu]

Hi:

I have added an helper column to get the results and have commented on values I am not clear.

Thanks

 

[tress]

Hi Nebu,

I have downloaded the file and looked at the couple of issues you have identified. The first issue H7, the corresponding result is correct, it was my expected result that wasn't. H11, in the initial post, if the Qty is 0 then the result is always 0 no matter what the ID is. I have added in some more records in to the example but when the formula get to H28, the formula seems to stop working even though there are matching values.

 

[Nebu]

Hi:

Find the attached. you did not increase the range, hence the values were missing. I have increased the range now and the formula is working fine.

Thanks