=if(vlookup........ Create in vba

[FACTOR 21]

Hi,

I have the following command

=IF(VLOOKUP(C348,'Trust Acc'!$A$2:$B$20,2,FALSE)<>0,VLOOKUP(C348,'Trust Acc'!$A$2:$B$20,2,FALSE),VLOOKUP(B348,Database!$A$672:$F$400670,6,FALSE))

which works fine and returns results as expected.

I know need to introduce exceptions to the final VLOOKUP, for example if the result was a match on "TEST" then "XXX" is returned, I will need to make approx 12 exceptions maybe more as time goes on.

Is it possible to create the command as VBA and easily set up and increase the exceptions as time goes by or do I have to continue expanding the original code?

Thanks

 

[Russell Hauf]

Hard to tell what you're trying to do from what you've written. Are you saying that B348 (in your example) may be 'TEST'? If so, could you add these "exceptions" into your "Database?" So column A would be 'TEST' and column F would be 'XXX'?

If this isn't what you're trying to do, I suggest adding more detail.

 

[FACTOR 21]

Currently the final look up checks cell B348 and compares it with a the range on the sheet 'Database', if B348 was 'TEST' and this was found in the database the contents of the cell in column 6 would be returned.

I need to now say if B348 was 'Invoices' and this was found on the 'database' then ignore the contents of the cell in column 6 and return 'XXX', I would also need to be able to do this for other matches.

I hope this makes sense.

Thanks for responding

 

[Russell Hauf]

Interesting. I suppose that you could do it either way you suggest above, but to me, a different solution would be the ideal one - maybe adding some sort of flag to your data or creating a small lookup table that has the "special" values in it - and check that small table before doing the lookup on the big ("Database") table (if found, no need to go further - if not found, do the lookup on the "Database").

 

[FACTOR 21]

Hi

I have made the table as suggested and amended the VLOOKUP as below

=IF(VLOOKUP(C348,'Trust Acc'!$A$2:$B$20,2,FALSE)<>0,VLOOKUP(C348,'Trust Acc'!$A$2:$B$20,2,FALSE),IF(VLOOKUP(B348,'Trust Acc'!$F$2:$G$20,2,FALSE)<>0,VLOOKUP(B348,'Trust Acc'!$F$2:$G$20,2,FALSE),VLOOKUP(B348,Database!$A$2:$F$400672,6,FALSE)))

The first two lookup's work fine and the correct results return, now the last look up fails and no results are returned, excel did not pick up any errors when I changed the Formula.

Any ideas would be appreciated

 

[Russell Hauf]

My suggestion is to break the formula up into a few cells and test it that way.