Apr 12 2019 07:25 AM
Hi,
I am trying to do multiple checks in Excel 2010. I want to look up a value and put it in a corresponding column. When there is no match I want it to check the first reference table for the closest match, then check that the Qty is correct, if so add in the Sal value. If Qty is 0 then Sal will be 0 no matter what the ID value says.
In my attachment I have put an example
In A1 to D8 is my reference table.
G1 to I10 is where I am trying to update values.
In the column I is my Index / Match, I3 to I5 make an exact match on the ID field and then show the correct Sal from the reference table. I6 to I9 have no exact Match and currently show "OH" just for reference.
In G6, this is very close to what is shown in A6 and as the Qty matches between the Reference records and the update records then I6 should show 50. The same rules will apply to G7 and G8.
If the Value in A appears anywhere in the field of column G, examples A123, 123A, CR123, etc and the Qty matches, then its a match.
I hope I have put enough details in but if anything else is needed please ask.
Thanks
Apr 12 2019 08:02 AM
Solution@harlequintp , that could be
=INDEX($C$2:$C$9,IF(ISNUMBER(G2),MATCH(G2,$A$2:$A$9,0),MATCH(1,INDEX((H2=$D$2:$D$9)*ISNUMBER(SEARCH(A2,$G$2:$G$9)),0 ),0)))
Apr 12 2019 08:11 AM
Apr 12 2019 08:44 AM
@harlequintp , here you first check if the value is number or not. If it's number when simple MATCH. If not, when within second MATCH the INDEX generates array of 1 or 0 (which is actually TRUE and FALSE) for your two criteria, multiplication means AND condition. MATCH takes position of 1 (aka TRUE) in that array, other words position of the row for which all your criteria match.
Apr 12 2019 08:51 AM
Apr 15 2019 12:38 AM
Hi Sergie,
Not sure if you will see this. You helped me last week on an Index/Match formula. I've just been sent the data that I need to apply the formula to and there is a small difference. In the ID column there I used the example of 456A, you have used the ISNUMERIC funtion 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 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 to show this.
Apr 15 2019 12:38 AM
Hi Sergie,
Not sure if you will see this. You helped me last week on an Index/Match formula. I've just been sent the data that I need to apply the formula to and there is a small difference. In the ID column there I used the example of 456A, you have used the ISNUMERIC funtion 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 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 to show this.
Apr 12 2019 08:02 AM
Solution@harlequintp , that could be
=INDEX($C$2:$C$9,IF(ISNUMBER(G2),MATCH(G2,$A$2:$A$9,0),MATCH(1,INDEX((H2=$D$2:$D$9)*ISNUMBER(SEARCH(A2,$G$2:$G$9)),0 ),0)))