# EMERGENCY!!!! Complex V-lookup HELP!!!

#### moonlight22

##### New Member
Hello,

I have a file which has multiple sheets. The first is my data sheet and the rest are destinations sheets.
The data is copied from another system and the destination sheets are required to pull very specific information with multiple factors to consider from the data sheet.

Basically this is what I need to accomplish:

In the data sheet there are reference codes, and account numbers. I currently have the complete list of account numbers which are split in my destination sheets by CAN and USD. They come mixed up in the extracted data which I paste into sheet1.
I do not have a complete list of the reference codes as they vary from month to month and can be 1 of thousands. Each code can correspond to any of the account numbers, and can vary monthly.

That said, I need for my destination sheet to find every occurrence of the code and the corresponding account number from sheet one. The tricky part being, if the code and account number appear more than once in sheet1 (which they can) I need the destination sheet to only pull it one. They also need to split into CAN or USD, which is identified by the last character of the account number (ie. 12345C or 12345U).

The destination sheet must have each code in column D, the corresponding account number in column A, and split by CAN and USD, as I have other formulas which pull other information based on these and sum certain numerical values by CAN and USD.

I am not sure if vlookup or hlookup is the way to go but that is all i could think of...

any help would be greatly appreciated.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try to post a small source sample and a corresponding destination output.

Hi,

here are samples...

Sample Data set:
 Acct Code Value 12345C ABC 500.00 12345C ABC 125.00 12345C ABC 956.00 12345C XYZ -456.00 12345C NMO 512.00 12345U NMO 362.00 12345U XYZ 1432.00 12345U XYZ 856.00 98765U XYZ -1256.00 98765U ABC 2563.00 98765U ABC 996.00 98765U ABC -50.00 98765C XYZ 785.00 98765C NMO 392.00 98765C NMO 441.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Sample Destination
 Acct Code Value 12345C ABC 1581.00 12345C XYZ -456.00 12345C NMO 512.00 98765C XYZ 785.00 98765C NMO 833.00 Sum CAD 3255.00 12345U NMO 362.00 12345U XYZ 2288.00 98765U XYZ -1256.00 98765U ABC 3509.00 Sum USD 4903.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

thanks!!

This looks like a job for a pivot table!

Any suggestions where I can get a quick 'how-to'... never created a pivot table before...

Just a quick note...I dont need any help with the summing of cost, as I have a separate formula for that. only the Acct and Code is what I need help with. But if the pivot table can accomplish it all then thats great as well!

Pivot table would accomplish it all. Here's a link:
PivotTable reports 101 - Excel

Try the following:
3. Go to the Insert tab, select insert pivot table, click ok
4. In the Field list, Add Acct and Code as "row labels" by dragging the fields to the appropriate box.
5. In the Field List,AddValue as a value field by dragging them to the appropriate box.

When you have a pivot table selected(or are clicked in one) an additional area will appear in your ribbon called "Pivot Table Tools." In the design tab of said section you can edit /modify the layout of your report. For your purposes you can look into a tabular layout with repeating row items. But you can play around with this section to get it to where you want.

You can add the CAN/USD as a column in your source data, or if the data is already seperated (in seperate ranges) you can just create a second pivot table.

Regardless of what solution may find you. I HIGHLY suggest checking out pivot tables. They are incredibly Powerful and are useful for many different tasks.

Any suggestions where I can get a quick 'how-to'... never created a pivot table before...

Just a quick note...I dont need any help with the summing of cost, as I have a separate formula for that. only the Acct and Code is what I need help with. But if the pivot table can accomplish it all then thats great as well!

If it boils down to it and you already have a sum if formula, couldn't you just use the remove duplicates in the data tab?

Sheet1, A1:B20, houses the source sample, the headers included.

Sheet2

 Type C Type U Idx Acct Code Value Idx Acct Code Value 1 12345C ABC 500 6 12345U NMO 362 2 12345C ABC 125 7 12345U XYZ 1432 3 12345C ABC 956 8 12345U XYZ 856 4 12345C XYZ -456 9 98765U XYZ -1256 5 12345C NMO 512 10 98765U ABC 2563 13 98765C XYZ 785 11 98765U ABC 996 14 98765C NMO 392 12 98765U ABC -50 15 98765C NMO 441

<COLGROUP><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1905" width=54><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 881" width=25><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2048" width=58><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

A3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IFERROR(SMALL(IF(RIGHT(Sheet1!\$A\$2:\$A\$16)=\$G\$1,
ROW(Sheet1!\$A\$2:\$A\$16)-ROW(Sheet1!\$A\$2)+1),ROWS(\$F\$3:F3)),"")
``````

G3, just enter, coy across, and down:
Rich (BB code):
``````=IF(\$F3="","",INDEX(Sheet1!A\$2:A\$16,\$F3))
``````

F3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IFERROR(SMALL(IF(RIGHT(Sheet1!\$A\$2:\$A\$16)=\$B\$1,
ROW(Sheet1!\$A\$2:\$A\$16)-ROW(Sheet1!\$A\$2)+1),ROWS(\$A\$3:A3)),"")
``````

B3, just enter, coy across, and down:
Rich (BB code):
``````=IF(\$A3="","",INDEX(Sheet1!A\$2:A\$16,\$A3))
``````

Replies
3
Views
84
Replies
11
Views
315
Replies
1
Views
125
Replies
14
Views
615
Replies
4
Views
229

1,212,099
Messages
6,105,957
Members
447,986
Latest member
dicklim39

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back