INDEX/MATCH or LOOKUP dilemma

Thanks:  0
Likes:  0

# Thread: INDEX/MATCH or LOOKUP dilemma

1. ## INDEX/MATCH or LOOKUP dilemma

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

 Key Serial B1 abc B2 xyz B3 456 B4 abc B5 xyz B6 456 B7 abc B8 xyz B9 abc B10 456 B11 xyz

2. ## Re: INDEX/MATCH or LOOKUP dilemma

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)))))))

 A B C D 1 Key Serial B8 2 B1 abc 3 B2 xyz 4 B3 456 5 B4 abc 6 B5 xyz 7 B6 456 8 B7 abc 9 B8 xyz 10 B9 abc 11 B10 456 12 B11 xyz

3. ## Re: INDEX/MATCH or LOOKUP dilemma

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

Coops

4. ## Re: INDEX/MATCH or LOOKUP dilemma

This seems to return the result of the row below.

5. ## Re: INDEX/MATCH or LOOKUP dilemma

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

6. ## Re: INDEX/MATCH or LOOKUP dilemma

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.

7. ## Re: INDEX/MATCH or LOOKUP dilemma

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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•