Complex vlookup is it possable.

jvoss

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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
In G2 of Own control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=INDEX(List!\$C\$2:\$C\$3,MATCH(\$A2,IF(List!\$B\$2:\$B\$3=\$F2,List!\$A\$2:\$A\$3),0))
``````

In G2 of Own control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=INDEX(List!\$C\$2:\$C\$3,MATCH(\$A2,IF(List!\$B\$2:\$B\$3=\$F2,List!\$A\$2:\$A\$3),0))
``````

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

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

You just did.

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>

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

Excel 2010
CDEFGHIJ
14-in-1 Fun Pak-17GBDMGF4USA1
24-in-1 Fun Pak-17GBDMGF4USA
34-in-1 Fun Pak-16GBDMGF4NOE
44-in-1 Fun PakGBDMGF4j-

<tbody>
</tbody>
Own

Excel 2010
CDEFGHI
15JapanDMGU4J-GB3-Fun Yosou Umaban Club
16Nintendo of EuropeDMGF4NOEGB4-in-1 Fun Pak-16
17United StatesDMGF4USAGB4-in-1 Fun Pak-17
18United StatesDMGF4USA1GB4-in-1 Fun Pak-18

<tbody>
</tbody>
List

Last edited:
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.

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:
Rich (BB code):
``````
=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))
``````

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?

Replies
1
Views
108
Replies
3
Views
416
Replies
3
Views
407
Replies
3
Views
1K
Replies
11
Views
1K

1,203,683
Messages
6,056,722
Members
444,887
Latest member
cvcc_wt

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.

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