cooper645
Well-known Member
- Joined
- Nov 16, 2013
- Messages
- 639
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- 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
<tbody>
</tbody>
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
Key | Serial |
B1 | abc |
B2 | xyz |
B3 | 456 |
B4 | abc |
B5 | xyz |
B6 | 456 |
B7 | abc |
B8 | xyz |
B9 | abc |
B10 | 456 |
B11 | xyz |
<tbody>
</tbody>