Index / Match alteration

[harlequintp]

Hi,

I recently posted a question and was given a great response but unfortunately the sample data missed one example that I am having trouble to apply to the formula.

In the ID column (A1) there I used the example of 456A, in the formula the ISNUMERIC funtion is used to strip out the A to then do a check against the numbers.  In the data I have been sent there can be something like 1ASN3Z in the G column and in my Ref table column (A) the letters SN.  In this case I would be looking for the SN in 1ASN3Z, the letter code and QTY will only every appear as unique so it will be still a 1 to 1 match, I need to still match the code plus the Qty values.  I am thinking it is just a case of removing the ISNUMERIC function but I am sure it wont be as easy as that.  Again my apologies that I wasn't sent all the possible examples of this.  I have re-uploaded an example with the working formula so far.

As a side note, unfortunately this cannot be done in VBA as the file I will be updating will be new every week so I am just looking to copy and paste the formula in to the sheet when I need it.

Thanks

[#2]

I believe that if the unique ID in Column G is not found in Column A, the lookup value must be a unique Qty in Column H.

[#3]

Hi @Twifoo, thanks for the reply, I am not sure if you are asking or making a statement.  If the value in column A is not found at all then no other checks need to be made and the Sal column can just be set to 0.  What I need to happen is to look for the value that appears in A, in the G column, if there is a match and the Qty match then add in the Sal value from C in to column I.  If the Qty in D is 0 then the Sal is also 0 no matter what the ID value in column G is.

[#4]

I made a declaration, not simply a a statement. Column A, based on your sample, is the basis from which values shall be retrieved. You cannot, and logic doesn’t allow you to, reverse the logic by insisting that, in certain instances, the lookup value will now become the lookup column.
It’s like going to a place you don’t know where, but you have an idea of its location. It now seems that you want the location, which you don’t know where, to give you an idea of the destination, which you also don’t know where.
There must be some pattern for a formula to work!

[#5]

My apologies @Twifoo I wasn't quite sure from your initial response.  In relation to my original question I am not looking to reverse logic by changing the lookup value to the lookup column.  But I do see some issues in my original logic as well as a strange error I am not sure about.  

As I mentioned columns A to D are my reference values and G to K is where I am updating the data to based on when the ID and Qty match or if Qty is 0 then Sal will be 0 no matter what the ID value says.

My example already does what I want it to do but what I have noticed is, if I add a new value to the ID column in the reference section column (A) it invalidates row 9 in where I am updating my data to which I don't understand where previously it was ok but new records added to the G to K section are fine.  Hopefully my example will show where the issue is.