Getting frustrated with excel formula! Help!

[serraostudent1]

I have the following formula working correctly! However it works correctly until it gets an error.

=iferror(vlookup(a2,a2:k450,6,0),"not in list")

Once the first "not in list " is returned
I need excel to start again by looking at the row above.

For example. Rows 2,3,4 all return column 6 correctly. Then Row5 was "not in list" excel should perform the above formula again but, start from row 4. Then loop until the end.

I hope this makes sense!

Thanks for taking the time to read my post!

Can you assist?

 

[serraostudent1]

Aladin,

I will dropbox in AM. Left my laptop at work. I am using iPad now.

 

[billszysz]

I didn't expect duplicate records...

Sheet2, E1, control+shift+enter and copy down:

=IFERROR(INDEX(Sheet1!$E$1:$E$7,
  SMALL(IF(Sheet1!$A$1:$A$7=$A1,IF(Sheet1!$B$1:$B$7=$B1,
  IF(Sheet1!$C$1:$C$7=$C1,IF(Sheet1!$D$1:$D$7=$D1,
  ROW(Sheet1!$E$1:$E$7)-ROW(Sheet1!$E$1)+1)))),
  COUNTIFS($A$1:A1,A1,$B$1:B1,B1,$C$1:C1,C1,$D$1:D1,D1))),"not in list")

Little shorter

=IFERROR(INDEX(Sheet1!$E$1:$E$7,SMALL(IF(MMULT(--(A1:D1=Sheet1!$A$1:$D$7),{1;1;1;1})=4,ROW($1:$7)),SUM(--(MMULT(--(A1:D1=Sheet2!$A$1:$D1),{1;1;1;1})=4)))),"Not in list")

Greetings

 

[serraostudent1]

Billszysz,
Tried your formula and received a circular reference, but it could not show me where. Any suggestions?

 

[mole999]

which cell did you put it in

 

[serraostudent1]

In cell e1. Should it be a1?

 

[Stridhan]

sheet2?

 

[serraostudent1]

Yes sheet 2!

 

[serraostudent1]

You probably won't be surprised by my ignorance. However, I got my formula to work after watching countless 'excel is fun' videos. I had to lock my column in my array section and it worked like a charm. Thanks again for taking your time to help a novice learn this stuff! Stay tuned for the next! Regards!

 

[Aladin Akyurek]

You probably won't be surprised by my ignorance. However, I got my formula to work after watching countless 'excel is fun' videos. I had to lock my column in my array section and it worked like a charm. Thanks again for taking your time to help a novice learn this stuff! Stay tuned for the next! Regards!

That's great. Thanks for providing feedback.