Complex vlookup is it possable.

J

jvoss

Board Regular
Joined
Jun 13, 2015
Messages
75
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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:[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B
[/TD]
[TD]Column C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tea[/TD]
[TD]AV[/TD]
[TD]USA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tea2[/TD]
[TD]AV[/TD]
[TD]NOE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tea2[/TD]
[TD]AV[/TD]
[TD]USA Not correct results[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tea[/TD]
[TD]AV[/TD]
[TD]USA correct results[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bread[/TD]
[TD]AV2[/TD]
[TD]NOT (answer from a 3rd sheet)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
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))
 
Upvote 0
Aladin Akyurek said:
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))
Click to expand...

Aladin,

WOW that was simple. now for the really fun part. where do i leave feedback for you?:nya::nya::nya::nya:
 
Upvote 0
now i have to expand to include a third cell to match.


List data Sheet Example:
[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column F
[/TD]
[TD]Column C
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]Tea1
[/TD]
[TD]AV[/TD]
[TD]USA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tea2
[/TD]
[TD]AV[/TD]
[TD]USA
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Tea3
[/TD]
[TD]AV
[/TD]
[TD]NOE
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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


[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column h
[/TD]
[TD]Column I
[/TD]
[TD]Column I
[/TD]
[/TR]
[TR]
[TD]Tea1
[/TD]
[TD]AV
[/TD]
[TD]USA[/TD]
[TD]1 (should be returning Tea2)
[/TD]
[/TR]
[TR]
[TD]Tea1
[/TD]
[TD]AV
[/TD]
[TD]USA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tea3
[/TD]
[TD]AV
[/TD]
[TD]NOE
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The current formula using in Result Data Sheet Column A is:
[TABLE="width: 449"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=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.
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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...
 
Upvote 0
Excel 2010[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]4-in-1 Fun Pak-17[/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]DMG[/TD]
[TD="bgcolor: #C5D9F1, align: center"]F4[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]4-in-1 Fun Pak-17[/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]DMG[/TD]
[TD="align: center"]F4[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]4-in-1 Fun Pak-16[/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]DMG[/TD]
[TD="align: center"]F4[/TD]
[TD="align: center"]NOE[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]4-in-1 Fun Pak[/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]DMG[/TD]
[TD="align: center"]F4j[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
Own
​

Excel 2010[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Japan[/TD]
[TD="align: center"]DMG[/TD]
[TD="align: center"]U4J[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"][/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"]3-Fun Yosou Umaban Club[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Nintendo of Europe[/TD]
[TD="align: center"]DMG[/TD]
[TD="align: center"]F4[/TD]
[TD="align: center"]NOE[/TD]
[TD="align: center"][/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"]4-in-1 Fun Pak-16
[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]United States[/TD]
[TD="align: center"]DMG[/TD]
[TD="align: center"]F4[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"][/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"]4-in-1 Fun Pak-17[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]United States[/TD]
[TD="align: center"]DMG[/TD]
[TD="align: center"]F4[/TD]
[TD="align: center"]USA[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]GB[/TD]
[TD="align: center"]4-in-1 Fun Pak-18[/TD]
[/TR]
</tbody>[/TABLE]
List
​
 
Last edited:
Upvote 0
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.
 
Upvote 0
jvoss said:
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.
Click to expand...

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))
 
Upvote 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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Nested VLOOKUP I think
Replies
4
Views
639
CordingBags
C
M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
294
Mouse7681
M
N
  • Question Question
Using formula to look up data where the data tab has the required data listed multiple times
Replies
1
Views
497
mumps
mumps
S
  • Solved
Lookup value using two separate dynamic ranges.
Replies
3
Views
791
Fluff
Fluff
V
  • Solved
Vlookup returns results in 1 column, but not another - help please
Replies
3
Views
552
Joe4
Joe4

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top