I am trying to insert the contents of a column from Sheet2 (VBAP in the attached) into Sheet1 (E1P). E1P may have multiples of the values in VBAP; I want the contents of the inserted column to repeat if there a mulitple values/occurrences. Right now, using the basic VLOOKUP, only the first occurrence of the 'wanted' value is returned.

Attached Files

VLookup_Example_08152012.xlsx (9.8 KB, 22 views)

=VLOOKUP($A6,VBAP!$A$1:$C$33,3,FALSE)

I'm not sure if I understand what you need.

I tried this, but it didn't do anything different.

Exactly my problem - I need something - match, lookup, index, that will look at the value in one sheet, see multiples of that value in the second sheet and return the associated value in the lookup/return column, repeatedly...

Not sure what you mean, this will give you a count of the values! Need some clarification on what you are trying to achieve!

=IF(COUNTIF($A$1:A2,$A2)=1,VLOOKUP($A2,VBAP!$A:$C, 3,FALSE)*COUNTIF($A$2:$A$34,$A2),"")

what I need to happen is this: lookup the value in E1P, cell A2, from the list of values in VBAP, A1:A33, then return the exact value from C1:C33 into the E1P table, column 2. What happens now, with the basic vlookup is that the first instance of the 'found' value in VBAP A1:A33 is being returned for each instance. I need the unique values from VBAP c2:c33 to be returned. column C in the E1P tables show what I need returned, but column b is what is being returned.

I have tried to build a formula that should give you the desired result. To explain the logic of what I have done:
1. Created an additional column called count in both the source (VBAP) and destination(E1P) sheets. The formula in all the cells of the Count column in the VBAP sheet gives the occurence number of the value in the "Sales Document" column. Occurrence number means whether it is the first occurence, second occurrence and so on of the "Sales Document" value in the sheet.

2. The formula in the Count column of the E1P sheet is more complicated. It checks to see if the "Document Head" value in E1P sheet exists in the "Sales Document" column of VBAP or not. If not, it returns 0. If the value exists, it counts the total number of occurrences of that value in VBAP sheet, and assigns the occurrence number to each value in the E1P sheet until it reaches the maximum number of occurrences in the VBAP sheet, and then starts from 1 again. E.g. if 13427653 occurs twice in VBAP, and occurs 4 times in E1P, the formula assigns occurrence number 1 to first occurrence, 2 to second occurrence, 1 to third occurrence, and 2 fourth occurrence.
3. The formula in Item column of E1P is a basic Index and Match formula that matches the values in the 2 columns of E1P with two columns of VBAP and returns the value from the Item column when there is a match.

I am attaching the file with the formulas.

Attached Files

2VLookup_Example_08152012.xlsx (13.8 KB, 16 views)

The Index and Match formula in the Item column of the E1P sheet is an array formula, so after you paste it in the formula files, press Ctrl+Shift+Enter. Once you have entered the formula in once cell, you can drag it down to the rest of the cells.