vLookup with embedded if statements?

C

Calboy

New Member
Joined
Feb 14, 2014
Messages
7
I'm hoping this is easier then I'm making it!

I'm trying to write some code that finds a part number in a large list by searching for key words in the descriptions for various models and then. Each of the models has a different part number which complicated this a little.

Does anybody have any ideas on what this code might look like. Can it use a VLookup with embedded If statements or something. I'm not very familiar with VBA syntax.



 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Superstar31 said:
Sample of your data might help
Click to expand...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Seq Number[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Model[/TD]
[TD]Part 1[/TD]
[TD]Part 2[/TD]
[TD]Part 3[/TD]
[/TR]
[TR]
[TD]Q's[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q1[/TD]
[TD]1204993[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]q2[/TD]
[TD]1204993[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]W's[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]w1[/TD]
[TD]1204995[/TD]
[TD]1323048[/TD]
[TD]1323049[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]w2[/TD]
[TD]1204995[/TD]
[TD]1323048[/TD]
[TD]1323049[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]w3[/TD]
[TD]1204995[/TD]
[TD]1323048[/TD]
[TD]1323049[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]w4[/TD]
[TD]1204947[/TD]
[TD]1323129[/TD]
[TD]1323083[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]w5[/TD]
[TD]1204947[/TD]
[TD]1323129[/TD]
[TD]1323083[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is what the data should look like after it's organized. There will be a lot more models and parts than this so it needs to be expandable too.

The Raw data is as follows:
[TABLE="width: 492"]
<tbody>[TR]
[TD]MODEL[/TD]
[TD]ENSEQ[/TD]
[TD]COMP[/TD]
[TD]DESC[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD]1000[/TD]
[TD]1204993[/TD]
[TD]THE WIDGET 1[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]1000[/TD]
[TD]1204993[/TD]
[TD]THE WIDGET 1[/TD]
[/TR]
[TR]
[TD]W1[/TD]
[TD]1000[/TD]
[TD]1204995[/TD]
[TD]THE WIDGET 1[/TD]
[/TR]
[TR]
[TD]W2[/TD]
[TD]1000[/TD]
[TD]1204995[/TD]
[TD]THE WIDGET 1[/TD]
[/TR]
[TR]
[TD]W3[/TD]
[TD]1000[/TD]
[TD]1204995[/TD]
[TD]THE WIDGET 1[/TD]
[/TR]
[TR]
[TD]W4[/TD]
[TD]1000[/TD]
[TD]1204947[/TD]
[TD]THE WIDGET 1[/TD]
[/TR]
[TR]
[TD]W5[/TD]
[TD]1000[/TD]
[TD]1204947[/TD]
[TD]THE WIDGET 1[/TD]
[/TR]
[TR]
[TD]RANDOM PART 3[/TD]
[TD]1000[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 4[/TD]
[TD]1000[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 9[/TD]
[TD]1500[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 10[/TD]
[TD]1500[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]W1[/TD]
[TD]1500[/TD]
[TD]1323048[/TD]
[TD]WIDGET 2[/TD]
[/TR]
[TR]
[TD]W2[/TD]
[TD]1500[/TD]
[TD]1323048[/TD]
[TD]WIDGET 2[/TD]
[/TR]
[TR]
[TD]W3[/TD]
[TD]1500[/TD]
[TD]1323048[/TD]
[TD]WIDGET 2[/TD]
[/TR]
[TR]
[TD]W4[/TD]
[TD]1500[/TD]
[TD]1323129[/TD]
[TD]WIDGET 2[/TD]
[/TR]
[TR]
[TD]W5[/TD]
[TD]1500[/TD]
[TD]1323129[/TD]
[TD]WIDGET 2[/TD]
[/TR]
[TR]
[TD]RANDOM PART 1[/TD]
[TD]1505[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 2[/TD]
[TD]1510[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 5[/TD]
[TD]1525[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 6[/TD]
[TD]1530[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 7[/TD]
[TD]1535[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 8[/TD]
[TD]1540[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 11[/TD]
[TD]1555[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 12[/TD]
[TD]1560[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 15[/TD]
[TD]1575[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]W1[/TD]
[TD]7000[/TD]
[TD]1323049[/TD]
[TD]Widget 3[/TD]
[/TR]
[TR]
[TD]W2[/TD]
[TD]7000[/TD]
[TD]1323049[/TD]
[TD]widget 3[/TD]
[/TR]
[TR]
[TD]W3[/TD]
[TD]7000[/TD]
[TD]1323049[/TD]
[TD]Widget 3[/TD]
[/TR]
[TR]
[TD]W4[/TD]
[TD]7000[/TD]
[TD]1323083[/TD]
[TD]Widget 3[/TD]
[/TR]
[TR]
[TD]W5[/TD]
[TD]7000[/TD]
[TD]1323083[/TD]
[TD]Widget 3[/TD]
[/TR]
[TR]
[TD]RANDOM PART 13[/TD]
[TD]7000[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
[TR]
[TD]RANDOM PART 14[/TD]
[TD]7000[/TD]
[TD]1323084[/TD]
[TD]RANDOM PART[/TD]
[/TR]
</tbody>[/TABLE]


Sorry I haven't figured out how add example sheets yet.
 
Upvote 0
I'm still not fully getting what everything looks like. Can you upload a sample or example on dropbox or something and link to it?
 
Upvote 0
Calboy said:
Here's a link to some example data

https://www.dropbox.com/s/9q2j5yhfwdz907l/example.xlsm
Click to expand...

here is a start, but not looking at desc (YET)


Excel 2010
ABCDE
1Sequence NUMBER100015007000
2Model #PART1PART2PART3
3WIDGET 1WIDGET 2WIDGET 3
4Q'S
5Q11204993#N/A#N/A
6Q21204993#N/A#N/A
7W'S#N/A#N/A#N/A
8W1120499513230481323049
9W2120499513230481323049
10W3120499513230481323049
11W4120494713231291323083
12W5120494713231291323083
Sheet1
Cell Formulas
RangeFormula
C5{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B5&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
C6{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B6&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
C7{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B7&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
C8{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B8&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
C9{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B9&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
C10{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B10&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
C11{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B11&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
C12{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B12&C$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D5{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B5&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D6{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B6&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D7{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B7&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D8{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B8&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D9{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B9&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D10{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B10&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D11{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B11&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
D12{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B12&D$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E5{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B5&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E6{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B6&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E7{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B7&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E8{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B8&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E9{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B9&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E10{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B10&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E11{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B11&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
E12{=INDEX('RAW DATA'!$C$2:$C$33,MATCH($B12&E$1,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Thanks, Superstar31-that's a great start for me. I'll work with it to see if I can get the description in there and post again if I am get it.
 
Upvote 0
I definitely haven't made any progress! I still need help on this one.

Thanks in advance everyone
 
Upvote 0
Superstar31 said:
So you need a 3 variable formula?
Click to expand...


Excel 2010
ABCDE
1Sequence NUMBER100015007000
2Model #PART1PART2PART3
3THE WIDGET 1WIDGET 2WIDGET 3
4
5Q11204993  
6Q21204993  
7   
8W1120499513230481323049
9W2120499513230481323049
10W3120499513230481323049
11W4120494713231291323083
12W5120494713231291323083
Sheet1
Cell Formulas
RangeFormula
C5{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B5&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
C6{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B6&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
C7{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B7&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
C8{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B8&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
C9{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B9&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
C10{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B10&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
C11{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B11&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
C12{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B12&C$1&C$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D5{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B5&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D6{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B6&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D7{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B7&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D8{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B8&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D9{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B9&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D10{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B10&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D11{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B11&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
D12{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B12&D$1&D$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E5{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B5&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E6{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B6&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E7{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B7&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E8{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B8&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E9{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B9&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E10{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B10&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E11{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B11&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
E12{=IFERROR(INDEX('RAW DATA'!$C$2:$C$33,MATCH($B12&E$1&E$3,'RAW DATA'!$A$2:$A$33&'RAW DATA'!$B$2:$B$33&'RAW DATA'!$D$2:$D$33,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Use a Vlookup only when a separate column contains a certain text.
Replies
7
Views
402
chewybar1986
C
wizau
  • Solved
Nested IF statements, or a better way?
Replies
4
Views
314
wizau
wizau
afrazier1221
  • Solved
VBA adding symbols, disabling the intended code. Please help.
Replies
1
Views
504
afrazier1221
afrazier1221
D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
715
portews
P
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
559
Davenil
D

Forum statistics

Threads
1,223,737
Messages
6,174,206
Members
452,551
Latest member
croud

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