vLookup with embedded if statements?

[Calboy]

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.

 

[Superstar31]

Now I just need to figured out how to make it search within the description instead of you having to use the actual word of the description

 

[dispelthemyth]

Edit: Slightly misunderstood

back to drawing board

 

[Calboy]

Now I just need to figured out how to make it search within the description instead of you having to use the actual word of the description

Thank You! It works great! I was able to solve searching within the description by doing something like *descriptive*words*. It's not pretty, but it works great! Thanks so much for the help. I've learned a lot.

 

[Superstar31]

Thank You! It works great! I was able to solve searching within the description by doing something like *descriptive*words*. It's not pretty, but it works great! Thanks so much for the help. I've learned a lot.

can you post the formula you used. i'm curious because i was stumped

 

[dispelthemyth]

I think its done with a slight annoyance at the minute, when the seach for the description yileds nothing it makes the row reference 0 which brings back a value due to it bening part of an Array,

Ive never come across this before so will have to work something out

Excel Workbook
	A	B	C	D	E
1		Sequence NUMBER	1000	1500	7000
2		Model #	PART1	PART2	PART3
3			WIDGET 1	WIDGET 2	WIDGET 3
4	Q'S
5		Q1	1204993	COMP	COMP
6		Q2	1204993	COMP	COMP
7	W'S
8		W1	1204995	1323048	1323049
9		W2	1204995	1323048	1323049
10		W3	1204995	1323048	1323049
11		W4	1204947	1323129	1323083
12		W5	1204947	1323129	1323083
Sheet1

 

[dispelthemyth]

done a workaround


on the bit that works out the row (the part of the formula below) i so 1/sum(1/row Ref)

this forces an error if the row reference is 0 and returns the same value if the row ref > 0

1/(1/SUMPRODUCT(--(('RAW DATA'!$A$1:$A$100=Sheet1!$B5)*('RAW DATA'!$B$1:$B$100=Sheet1!D$1)*(IFERROR(SEARCH(D$3,'RAW DATA'!$D$1:$D$100,1),0)>0))

Excel Workbook
	A	B	C	D	E
1		Sequence NUMBER	1000	1500	7000
2		Model #	PART1	PART2	PART3
3			WIDGET 1	WIDGET 2	WIDGET 3
4	Q'S
5		Q1	1204993
6		Q2	1204993
7	W'S
8		W1	1204995	1323048	1323049
9		W2	1204995	1323048	1323049
10		W3	1204995	1323048	1323049
11		W4	1204947	1323129	1323083
12		W5	1204947	1323129	1323083
Sheet1

 

[dispelthemyth]

A better written formula below

Excel Workbook
	A	B	C	D	E
1			1000	1500	7000
2		Model #	PART1	PART2	PART3
3			WIDGET 1	WIDGET 2	WIDGET 3
4	Q'S
5		Q1	1204993
6		Q2	1204993
7	W'S
8		W1	1204995	1323048	1323049
9		W2	1204995	1323048	1323049
10		W3	1204995	1323048	1323049
11		W4	1204947	1323129	1323083
12		W5	1204947	1323129	1323083
Sheet1

 

[Calboy]

The equations work great, but now I'm running into a different problem. It seems like there is a glitch in excel. What I am doing is I'm running a VBA program I wrote to compare the part numbers from a previous year and highlighting parts that were used in last years models. The program works great when I hand type in the number, but when I try it on the information that output from the equation the program doesnt work. I've tried copying the data and pasting values and it still doesn't work; however, when I click on the copied cell and put the cursor at the end of the value and hit the delete key then hit enter, the program will work fine for that cell. Do you have any recommendations?

Here is the VBA code I'm running.
Sub dup() Dim cell As Range, cella As Range, rng As Range, srng As Range
Set rng2 = Sheets("MY14").Range("d3:ag110")
Set rng3 = Sheets("MY15(2)").Range("d5:ah120")
For Each cell In rng2
For Each cella In rng3
If cella > "" Then
If cella = cell Then
cella.Interior.ColorIndex = 10

End If
End If

Next cella
Next cell


End Sub

 

[Superstar31]

The equations work great, but now I'm running into a different problem. It seems like there is a glitch in excel. What I am doing is I'm running a VBA program I wrote to compare the part numbers from a previous year and highlighting parts that were used in last years models. The program works great when I hand type in the number, but when I try it on the information that output from the equation the program doesnt work. I've tried copying the data and pasting values and it still doesn't work; however, when I click on the copied cell and put the cursor at the end of the value and hit the delete key then hit enter, the program will work fine for that cell. Do you have any recommendations?

Here is the VBA code I'm running.
Sub dup() Dim cell As Range, cella As Range, rng As Range, srng As Range
Set rng2 = Sheets("MY14").Range("d3:ag110")
Set rng3 = Sheets("MY15(2)").Range("d5:ah120")
For Each cell In rng2
For Each cella In rng3
If cella > "" Then
If cella = cell Then
cella.Interior.ColorIndex = 10

End If
End If

Next cella
Next cell


End Sub

VBA is not my cup of tea.