software

This question is touched on in the post quot;Expert VLookupsquot;.

I have two worksheets with numerous columns in each and about 1,000 rows
each. Column A in both worksheets has company names. Each company name could
have multiple entries. Against the multiple entries are numerous pieces of
data including the payment amount.

In spreadsheet 1 I want to find the invoice number from spreadsheet 2 where
quot;bothquot; the company name and the payment amount are identical.

Example

Spreadsheet 1
Company Col B Col C Col D Col E Pymt Amt Col F Col G Invoice
XYZ 6421.00
0012472
XYZ 3736.42
0012834
XYZ 7224.56
0011942
XYZ 4337.88
0013652In spreadsheet 2 I want to lookup the invoice number where the company is
XYZ and the Pymt Amt is $7,224.56.

Do I need to use an 'array' or is there a worksheet function that can be used?

Hi!

I'm confused about which sheet is which!

Try something like this and just plug in your sheet names/ranges.

Array entered using the key combo of CTRL,SHIFT,ENTER:

A1 = XYZ
B1 = 7224.56

=INDEX(Invoice_range,MATCH(1,(Company_range=A1)*(P ayment_range=B1),0))

Biff

quot;KopRedquot; gt; wrote in message
...
gt; This question is touched on in the post quot;Expert VLookupsquot;.
gt;
gt; I have two worksheets with numerous columns in each and about 1,000 rows
gt; each. Column A in both worksheets has company names. Each company name
gt; could
gt; have multiple entries. Against the multiple entries are numerous pieces of
gt; data including the payment amount.
gt;
gt; In spreadsheet 1 I want to find the invoice number from spreadsheet 2
gt; where
gt; quot;bothquot; the company name and the payment amount are identical.
gt;
gt; Example
gt;
gt; Spreadsheet 1
gt; Company Col B Col C Col D Col E Pymt Amt Col F Col G Invoice
gt; XYZ 6421.00
gt; 0012472
gt; XYZ 3736.42
gt; 0012834
gt; XYZ 7224.56
gt; 0011942
gt; XYZ 4337.88
gt; 0013652
gt;
gt;
gt; In spreadsheet 2 I want to lookup the invoice number where the company is
gt; XYZ and the Pymt Amt is $7,224.56.
gt;
gt; Do I need to use an 'array' or is there a worksheet function that can be
gt; used?
gt;
gt;
gt;
Is it possible that the same company could have multiple invoices
with the same value?
If so, do you want the first match, the last match, or all matches
returned?
Rgds,
ScottO

quot;KopRedquot; gt; wrote in message
...
| This question is touched on in the post quot;Expert VLookupsquot;.
|
| I have two worksheets with numerous columns in each and about 1,000
rows
| each. Column A in both worksheets has company names. Each company
name could
| have multiple entries. Against the multiple entries are numerous
pieces of
| data including the payment amount.
|
| In spreadsheet 1 I want to find the invoice number from spreadsheet
2 where
| quot;bothquot; the company name and the payment amount are identical.
|
| Example
|
| Spreadsheet 1
| Company Col B Col C Col D Col E Pymt Amt Col F Col G
Invoice
| XYZ 6421.00
| 0012472
| XYZ 3736.42
| 0012834
| XYZ 7224.56
| 0011942
| XYZ 4337.88
| 0013652
|
|
| In spreadsheet 2 I want to lookup the invoice number where the
company is
| XYZ and the Pymt Amt is $7,224.56.
|
| Do I need to use an 'array' or is there a worksheet function that
can be used?
|
|
|