INDEX/MATCH or LOOKUP dilemma

[cooper645]

Hi guys and gals.

So I am having trouble coming up with a solution to my problem.
The scenario I have is an auto populating form based on one key cell.

I select the serial number and the rest of the form fills out based on that selection, all of this work fine, until I get to the point that I need to lookup the previous instance of a Serial Number in a list.

So I Select Key B11 and I need to lookup the previous instance of the associated unique serial xyz. (the serials are repeated as a history and struck out)
The result that should be returned would be B8

The Key is in column A:A
The serial is in column B:B

I have tried: =INDEX(A1:A30,MATCH(SerNum,B1:B30,0),0) - which returns the first instance
I have tried: =IF(COUNTIF(B:B,SerNum),LOOKUP(2,1/(B:B=SerNum),A:A),""). - which returns the last instance.

any help is appreciated.

Kind Regards,
Coops

[TABLE="class: grid, width: 4, align: left"]
<tbody>[TR]
[TD]Key[/TD]
[TD]Serial[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]B8[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]B9[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]B10[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]B11[/TD]
[TD]xyz[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

Try this:- Ctrl+Shift+Enter NOT just Enter

Change the red value as needed...

D1 =INDEX($A$1:$A$30,MAX(($B$1:$B$30=INDEX($B$1:$B$30,MATCH("B11",$A$1:$A$30,0)))*ROW($A$1:$A$30)*(ROW($A$1:$A$30) < SUM(((A1:A30="B11")*(ROW($A$1:$A$30)))))))


[TABLE="width: 350"]
<tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Key[/TD]
[TD="class: xl63"]Serial[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]B8[/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]B1[/TD]
[TD="class: xl63"]abc[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]B2[/TD]
[TD="class: xl63"]xyz[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]B3[/TD]
[TD="class: xl63"]456[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]B4[/TD]
[TD="class: xl63"]abc[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]B5[/TD]
[TD="class: xl63"]xyz[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]B6[/TD]
[TD="class: xl63"]456[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]B7[/TD]
[TD="class: xl63"]abc[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]B8[/TD]
[TD="class: xl63"]xyz[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]B9[/TD]
[TD="class: xl63"]abc[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]B10[/TD]
[TD="class: xl63"]456[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]B11[/TD]
[TD="class: xl63"]xyz[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

 

[cooper645]

Thanks Admiral,
I will try and give it a go when I get back into the office tomorrow.

Coops

 

[cooper645]

This seems to return the result of the row below.

 

[admiral100]

Correct , the previous instance....Is that what you asked for?

 

[cooper645]

Ah, I think I know where it went wrong, the ROW part uses all of tjem
whereas I tested it with a range from A2, so I simply need to add -1 in there.

I can then then adapt it to my actual worksheet where the data starts from ROW 17.

im sure I can get this to work for me.

Thanks for for taking the time and I will post back next week sometime when I figure it out or my brain dries.

 

[cooper645]

Although I can get this working, when I try to implement it onto my actual sheet, I hit a snag, as the lookup data is on a different tab to the lookup value.

Is is there a simple fix or would VBA be a better choice for this problem?