Multiple V Lookups

[Guest]

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?

 

[Biff]

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

 

[ScottO]

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

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