INDEX/MATCH or LOOKUP dilemma

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
639
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

KeySerial
B1abc
B2xyz
B3456
B4abc
B5xyz
B6456
B7abc
B8xyz
B9abc
B10456
B11xyz

<tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)))))))


ABCD
1KeySerialB8
2B1abc
3B2xyz
4B3456
5B4abc
6B5xyz
7B6456
8B7abc
9B8xyz
10B9abc
11B10456
12B11xyz

<tbody>
</tbody>
 
Last edited:
Upvote 0
Thanks Admiral,
I will try and give it a go when I get back into the office tomorrow.

Coops
 
Last edited:
Upvote 0
This seems to return the result of the row below.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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