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?

 

[mole999]

I totally MIS understood what you were after

 

[Aladin Akyurek]

List A is located in A:E in Sheet1; List B in A:D in Sheet2...

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

=IFERROR(INDEX(Sheet1!$E$1:$E$7,MATCH(1,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,1)))),0)),"not in list")

 

[Stridhan]

Genius formula above but, wont get the last value right?
Last to rows are exact duplicates so the formula gives the first duplicates value.

Learning alot by watching mr Aladins solutions so looking forward for the extra twist on this one

 

[Aladin Akyurek]

Genius formula above but, wont get the last value right?
Last to rows are exact duplicates so the formula gives the first duplicates value.

Learning alot by watching mr Aladins solutions so looking forward for the extra twist on this one

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

 

[Stridhan]

ray:

 

[Aladin Akyurek]

ray:

That means you are happy. Great.

 

[serraostudent1]

Ok it is doing something! Lol! It's not you it's me the novice here, still working on it! The first time I received a "not in list" in all rows. Next I received all 3000, so I got first record! Yeah me! Then I got all 0's. Bah humbug! Will keep trying!

 

[Aladin Akyurek]

Ok it is doing something! Lol! It's not you it's me the novice here, still working on it! The first time I received a "not in list" in all rows. Next I received all 3000, so I got first record! Yeah me! Then I got all 0's. Bah humbug! Will keep trying!

https://dl.dropboxusercontent.com/u/65698317/public serraostudent1.xlsx

 

[mole999]

Ok it is doing something! Lol! It's not you it's me the novice here, still working on it! The first time I received a "not in list" in all rows. Next I received all 3000, so I got first record! Yeah me! Then I got all 0's. Bah humbug! Will keep trying!

did you Sheet2, E1, control+shift+enter and copy down: so it is entered as an array formula, with curly braces

 

[serraostudent1]

did you Sheet2, E1, control+shift+enter and copy down: so it is entered as an array formula, with curly braces

I did control+shift+enter! Is there a way for me to break the formula into little pieces? So I can add one part at a time? I.e index..then row...then..count if?

Thanks for your help here!I am learning so much!!!!