VLOOKUP the first result, then the second, then the third....

[ThatExcelLife]

Hey guys

Firstimer here! Woohoo!

I got an interesting problem. What I am trying is to do create a formula for which VLOOKUP will use column A in Exhibit 1, vlookup the info from Exhibit 2 and return the value in column B Exhibit 2. There is an extra vlookup that will use the contract number to find the machine number, and the machine number will then look up the category number, so 0997550216 to UG to 1201. Now a simple vlookup will do the 1201 nicely. The problem is vlookup ONLY looks and takes the first value it finds! What I need the formula to do is now, where the question mark is in exhibit 1, return the SECOND category number, or 1217 from Exhibit 2. What could I do so in Exhibit 1, I can have a formula I can simply pull down based on the contract number in column A and excel returns the first, second, third categories as needed? Something like a "If vlookup result is the same in cell above, then take the next available one"? I don't know. Its a tricky one.

Exhibit 1 [TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78"]CONTRACT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121"]CATEGORY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78"]0997550216[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121"]1201[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78"]0997550216[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78"]0997550218[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Exhibit 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl67, width: 130"]MACHINE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl67, width: 93"]CATEGORY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]UG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]1201[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]UG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]1217[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]CAID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]1301[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the forum!

so 0997550216 to UG

How do we know that "0997550216" is "UG" ?

 

[ThatExcelLife]

Thanks for the welcome!

There is a third exhibit that serves as a mapping of sorts, so my current vlookup would be =VLOOKUP(VLOOKUP(A2,Exhibit 3$A:$B,2,FALSE) Exhibit 2$A:$B,2,FALSE). This goes from 0997550216 to UG to 1201.

 

[FormR]

There is a third exhibit that serves as a mapping of sorts

OK thanks, here is one option that you can try to adapt to your actual set-up:

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Exibit 1			Exibit 2			Exibit 3
2	CONTRACT	CATEGORY		MACHINE	CATEGORY		Contract	Machine
3	997550216	1201		UG	1201		997550216	UG
4	997550216	1217		UG	1217		997550218	CAID
5	997550218	1301		CAID	1301
Sheet1

Cell Formulas
Range	Formula
B3	=INDEX($E$3:$E$100,AGGREGATE(15,6,(ROW($E$3:$E$100)-ROW($E$3)+1)/($D$3:$D$100=VLOOKUP(A3,$G$3:$H$4,2,0)),COUNTIFS(A$3:A3,A3)))

 

[ThatExcelLife]

HOW did you figure this out!?! Its works perfectly! You know how long I have been attempting to think this through? Wow a HUGE thanks to you, MrExcel MVP. Really helps me out. Please chalk this problem up as SOLVED.