Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple V Lookups
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 "KopRed" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple V Lookups
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" wrote in message ... | 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? | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting multiple sheet tabs and open another workbook | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Multiple Lookups | Excel Discussion (Misc queries) | |||
multiple lookups - xls2003 | Excel Worksheet Functions |