Forum Discussion

harlequintp's avatar
harlequintp
Copper Contributor
Apr 15, 2019

Index / Match alteration

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

excel
Formulas and Functions

4 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Apr 16, 2019
    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.
    • harlequintp's avatar
      harlequintp
      Copper Contributor
      Apr 16, 2019

      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.

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Apr 16, 2019
        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!

Resources