Amend Vlookup formula if result returns #N/A

[flupsie]

I have the following Vlookup formula below to extract the account number on a workbook called account number in Col G on Sheet1


The formula works fine. However, if there are leading zeroes pertaining to the reference no. , a dash in the ref no. etc, a #N/A wil be returned

I would like my formula amended so that where a #N/A is returned, then the values to be matched , so that the applicable account no. in Col G is returned. If the value in the source data is negative , it must be matched to the value in the credit col I from the source workbook (Account numbers.xls) and postive values matched with the value in Col H, so that the relevant account number is extracted


I have attached sample workbooks


It would be appreciated if someone could kindly amend my formula to accomodate the above request

[jason.b75]

The formula works fine.

Then you must be using a completely different formula to the one in your sample file, that one will not return anything, even if it did, it would only return the correct result by chance because it doesn't compare the credit / debit values.

Try

=INDEX('[Account Numbers.xlsx]Sheet1'!$E$2:$E$3,MATCH(2,1/((RIGHT('[Account Numbers.xlsx]Sheet1'!$A$2,LEN(B2))=(B2&""))*IF(D2<0,'[Account Numbers.xlsx]Sheet1'!$I$2:$I$3=ABS(D2),'[Account Numbers.xlsx]Sheet1'!$H$2:$H$3=D2))))

Array confirmed with shift ctrl enter.

[flupsie]

Hi Jason

Thanks for your help. The result in H2 is correct. The value is negative in D2 so it should match with the value in I2 on workbook Account numbers

However the result in H3 should be 99238. The value is positive in D3 so it should match with the value in H3 on workbook Account numbers


Kindly amend your formula to accomodate the above


BTW please explain what MATCH(2,1/((RIGHT('[Account Numbers.xlsx]Sheet1'!$A$2,LEN(B2))=(B2&"")) result in ?

I have used match & index before but do not know what the 2,1/right(......... means

[jason.b75]

I'm guessing that when you entered the formula into your sheet, you did it by selecting both cells, then pressing f2 and pasting the formula.

If you enter the formula into H2, confirm the array, then use the fill handle to copy down it works correctly.

In the formula, RIGHT() is used to compare the reference numbers in the sales report to those in the account number list, the leading zero's are on the left, the useful part is on the right. This creates an array of true (match) and false (no match)

The next part, IF(D2<0,... creates an array of true and false by comparing the value in column D in the sales report to the credit and debit values in the account list.

The 2 arrays are then multiplied, with the principle of true*true = 1, anything else returns 0. At this point the whole array will be 0's except for the position of the correct match.

The preceding division, 1/ then forces errors in place of 0's. Matching 2 to the array finds the remaining 1 which gives the row of the correct result in then table.

This is a method that tends to be habit with match arrays like this, although for your purpose, the 2,1/(right and approximate match could be replaced with 1,(right and exact match and still get the correct result.

[flupsie]

Thanks for your explanation Jason. It makes perfect sense

Your were 100% correct that I entered the array by selecting H2 & H3. I have now copied it into H2 and used the fill handle to drag the formula down and it works perfectly

[flupsie]

Hi Jason

After testing and using your formula which you kindly provided, I need the formula to be amended so that where the values in the destination and source file as well as ref number are the same (including leading zeroes, which your formula does take into account) , the account number in Col E in the source file is to extracted


For Eg if credit value in Col I in source file is 89912.29 and it matches to Col D in destination file -89,912.29 and the ref no. in Col A in source file including leading zeroes 0008698 agrees to ref in Col B in destination file 8698, then extract account number in Col E. In the sample file previously attached it is 99235


It would be appreciated if you would kindly amend the formula to accomodate the above request

[jason.b75]

I'm confused, as far as I can see, what you're asking is what the formula already does. Am I missing something?

[flupsie]

Hi Jason

Sorry for any confusion caused


Attached please find sample files. Layout slightly different, but it is returning #N/A. Not sure why. Please check and amend formula if necessary

I have just picked up that several of the references and values can be the same. Where this is the case, then the account numbers that match the value and ref number to be extracted in Col M, col N etc


It would be appreciated if you could test the formula on workbook "Account Value and Ref Test.xlsm" This is the file the formula is to be used on


The source data is on workbook Account Number Template 2017.xlsm

[jason.b75]

The multiple correct matches are part of the problem.

In your latest example, rows 6 and 8 of the account template both met the criteria for the first row in the ref test, yet you only have one record to be matched in value and ref test, so which code do you expect to be returned?

[flupsie]

Hi Jason
I had my options set to manual calculation.

Where the ref & value matches and the ref appears more than once, then the first account number for each of the references to be extracted where the values also match


I have highlighted the correrct account numbers to be extracted in Col N


Hope this is clearer and once again apologies for any confusion. I appreciate all the help and input so far

[jason.b75]

I think that this should do the trick.

=INDEX('[Account Number Template 2017.xlsm]Data Import'!$G$2:$G$18,MATCH(1,(RIGHT('[Account Number Template 2017.xlsm]Data Import'!$A$2:$A$18,LEN(G2))=(G2&""))*IF(I2<0,'[Account Number Template 2017.xlsm]Data Import'!$J$2:$J$18=ABS(I2),'[Account Number Template 2017.xlsm]Data Import'!$I$2:$I$18=I2),0))

[flupsie]

Hi Jason


Thanks very much for all the effort. Your code works perfectly

[AliGW]

Please note that code is something different - what Jason has given you is a formula.

[flupsie]

When I posted my proble,, I thought that if there is no suitable formula, then the only other alternative would be to use code to give me the correct result.


Jason's formula provided me with the correct solution