Complex vlookup is it possable.

[jvoss]

Hi Everyone.

I have a very complex (or simple i hope) problem.

Here it is,

I have 2 sheets:
First Sheet I'll call "list" (Raw Data that is ever growing). 12+ Columns and growing & 10000 Rows.
Second Sheet I'll call "own" (yes this for a growing collection of items that i own.

(i'll post a workbook soon if needed)

in the "list" one column "b" started out as a unique value(value never repeats) no problem for Vlookup. but as the "list" grows this unique column "b" has grown to be not so unique. eg the value has been repeating.

in the "list" column "b" is associated to column "c" how is one of ~12 values.

example:

Column A	Column B	Column C
Tea	AV	USA
Tea2	AV	NOE

<tbody>
</tbody>

In "Own" sheet i have a column F is the manual input column G and A are results of a vlookup in "list" worksheet.

Column A	Column F	Column G
Tea2	AV	USA Not correct results
Tea	AV	USA correct results
Bread	AV2	NOT (answer from a 3rd sheet)

<tbody>
</tbody>

Current code for Column "A" sheet "Own" is =IF(IFERROR(VLOOKUP(A2,List!B:B,1,FALSE),"")=A2,VLOOKUP(H176,List!B:C,2,FALSE),VLOOKUP(A2,List_2!E:H,4,FALSE))

for this example "List_2" dose not matter.

Using formulas how do i get the correct results???

Yes i have tried Index,Match no luck(i could be entering the wrong syntax),vlookup +1,
have not tried the helper column before 10000 items on raw sheet.

Need to do this with out reworking the RAW sheet. IS THIS POSSIBLE??????

 

[Aladin Akyurek]

In G2 of Own control+shift+enter, not just enter, and copy down:

=INDEX(List!$C$2:$C$3,MATCH($A2,IF(List!$B$2:$B$3=$F2,List!$A$2:$A$3),0))

 

[jvoss]

In G2 of Own control+shift+enter, not just enter, and copy down:

=INDEX(List!$C$2:$C$3,MATCH($A2,IF(List!$B$2:$B$3=$F2,List!$A$2:$A$3),0))

Aladin,

WOW that was simple. now for the really fun part. where do i leave feedback for you?

 

[Aladin Akyurek]

Aladin,

WOW that was simple. now for the really fun part. where do i leave feedback for you?

You just did.

 

[jvoss]

now i have to expand to include a third cell to match.


List data Sheet Example:

Column A	Column F	Column C	Column C
Tea1	AV	USA
Tea2	AV	USA	1
Tea3	AV	NOE

<tbody>
</tbody>

Result data Sheet Example with Column H,I,I manual input Column A is the Formula.

Column A	Column h	Column I	Column I
Tea1	AV	USA	1 (should be returning Tea2)
Tea1	AV	USA
Tea3	AV	NOE

<tbody>
</tbody>

The current formula using in Result Data Sheet Column A is:

=INDEX(List!$I$2:$I$3970,MATCH($H3,IF(List!$F$2:$F$3970=$I3,List!$G$2:$G$3970),0))+control+shift+enter please explain for my understanding.

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

 

[Aladin Akyurek]

Hard to follow...

Would you post a sample from the List sheet and a sample the results sheet? Do not refer to the formula, just the outcome that must obtain...

 

[jvoss]

Excel 2010

	C	D	E	F	G	H	I	J
1	4-in-1 Fun Pak-17	GB			DMG	F4	USA	1
2	4-in-1 Fun Pak-17	GB			DMG	F4	USA
3	4-in-1 Fun Pak-16	GB			DMG	F4	NOE
4	4-in-1 Fun Pak	GB			DMG	F4j	-

<tbody>
</tbody>

Own
​

Excel 2010

	C	D	E	F	G	H	I
15	Japan	DMG	U4J	-		GB	3-Fun Yosou Umaban Club
16	Nintendo of Europe	DMG	F4	NOE		GB	4-in-1 Fun Pak-16
17	United States	DMG	F4	USA		GB	4-in-1 Fun Pak-17
18	United States	DMG	F4	USA	1	GB	4-in-1 Fun Pak-18

<tbody>
</tbody>

List
​

 

[jvoss]

The List sheet contains about 4000 items, the "own" sheet is the result sheet. We input the data in Own!H-J.
The "lookup" goes to the "List" sheet and returns the results of the match into Own!C.

The problem you see is that i also need to see if Own!J has a number if it dose then also use as a match requirement to return the correct name in this case it happens to be List!I18.

 

[Aladin Akyurek]

The List sheet contains about 4000 items, the "own" sheet is the result sheet. We input the data in Own!H-J.
The "lookup" goes to the "List" sheet and returns the results of the match into Own!C.

The problem you see is that i also need to see if Own!J has a number if it dose then also use as a match requirement to return the correct name in this case it happens to be List!I18.

In C1 of Own control+shift+enter, not just enter, and copy down:

=INDEX(List!$I$15:$I$18,MATCH($H1,IF(List!$F$15:$F$18=$I1,
    IF(List!$G$15:$G$18=$J1,List!$E$15:$E$18)),0))

 

[jvoss]

You did it again. Can i get you a soda? that is the least i can do.

I was close, so if i need to add more requirements i just put in a "IF" statement as true? and as long as the last "List" is always the same?