Use an array formula to perform a two or more criteria lookup in Excel

If we assume all invoice references are numbers and 0 never valid then given external references etc I'd suggest something like:

Please Login or Register  to view this content.

The above can be copied to Cols D & H also - note in your sample you have cust. code & LC Number mixed up in D & H

For the amount - use a standard VLOOKUP - eg:

Please Login or Register  to view this content.

No attachment.

I presume you noted that the formula in A7 is an Array and must be confirmed with CTRL + SHIFT + ENTER ?
Confirming the same with Enter (as you would normally) will not suffice in this instance.

Error on my part...

Please Login or Register  to view this content.

however, this still won't generate the "results should be" given it matches the LC number

Originally Posted by cyee

I'm trying to create a formula in "LC monitoring" that look up for invoice no and invoice amount which match "LC number".

only 10010254 shares that value (the others though same supplier have different LC number)

9.99E+307 is a very big number ... 9.99*10^307

Used in the context of this LOOKUP it is used to retrieve the last numeric value from the lookup_vector

Here the lookup_vector is populated with 2 values [(c/o CHOOSE({1,2},...)]
the first value being the default "error handler" value of 0
the second value being the output of the INDEX array

It follows that if the Array returns a valid number then this will be returned by the LOOKUP (last number) else if it should return an error it will return 0 (last number).

Following on from your other thread - it would still be better to return a COUNT of the LC number from the other sheet (file) in one cell and use that as basis for establishing whether or not the INDEX array need be performed.

Using the above construct the Array is always performed once per cell which is less than ideal IMO.
It is still preferable to the construct used in your other thread (double evaluation) in which the Array is performed at least once but sometimes twice per cell (twice if valid result)

In XL2007 MS finally introduced the long overdue IFERROR function to prevent need for double evaluation. The LOOKUP approach is limited in scope unlike IFERROR.
Many developers have VBA (UDF) equivalents of IFERROR in earlier versions but for most excel users a formula handler is preferable (ie no VBA requirements)

I think this is related to the #REF! problem you have raised in your other thread which I have answered in the last few minutes.

In answer to your question: no change required: the issue you have is outlined above (and in the other thread)

Sorry, I'm an idiot ... yes it will cause issues because the LOOKUP is looking explicitly for numbers.

To cater for multiple types without need for double evaluation of Array

Please Login or Register  to view this content.

then Modify VLOOKUP accordingly

Please Login or Register  to view this content.

in truth it would be an idea to store the count of invoices in a separate calculation and use that to determine as to whether or not you need conduct the Array in the first instance.