Multiple V Lookups

This question is touched on in the post "Expert VLookups".

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
"both" 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?

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)*(Payment_range=B1),0))

Biff

"KopRed" <KopRed@discussions.microsoft.com> wrote in message
news:3DE53868-0B12-4D45-9DCE-5354D78ABE55@microsoft.com...
> This question is touched on in the post "Expert VLookups".
>
> 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
> "both" 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?
>
>
>

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

"KopRed" <KopRed@discussions.microsoft.com> wrote in message
news:3DE53868-0B12-4D45-9DCE-5354D78ABE55@microsoft.com...
| This question is touched on in the post "Expert VLookups".
|
| 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
| "both" 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?
|
|
|