Getting frustrated with excel formula! Help!

S

serraostudent1

New Member
Joined
Nov 17, 2010
Messages
45
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
is it a formula to detect if value is missing in column A or is it misspelled above?
 
Upvote 0
If your list is in order then

change ,0) to ,1)

there is no point using not in list if you want to change that value as you get back to =vlookup(a2,a2:k450,6,1)
 
Upvote 0
aladin,

Thanks but that is what I currently use. But what I need is the language to tell excel to look one row above " not in list" and start again.
 
Upvote 0
List A
1Certified StaffGogo Dancers1REG PAY3000
2Certified StaffGogo Dancers2TAS DIFFERENTIAL1910.84
3Certified StaffEager Evon1REG PAY2535.16
4Certified StaffFond Frances1REG PAY2519.11
5Certified StaffRonald Rat1REG PAY1577.89
6Certified StaffDaffy Duck1REG PAY$2,386.93
7Certified StaffDaffy Duck1REG PAY$2,635.16
List B
1Certified StaffGogo Dancers1REG PAY
2Certified StaffGogo Dancers2TAS DIFFERENTIAL
3Certified StaffHickory Dickory1REG PAY
4Certified StaffFond Frances1REG PAY
5Certified StaffRonald Rat1REG PAY
6Certified StaffDaffy Duck1REG PAY
7Certified StaffDaffy Duck1REG PAY
Desired Result
1Certified StaffGogo Dancers1REG PAY3000
2Certified StaffGogo Dancers2TAS DIFFERENTIAL1910.84
3Certified StaffHickory Dickory1REG PAYnot in list
4Certified StaffFond Frances1REG PAY2519.11
5Certified StaffRonald Rat1REG PAY1577.89
6Certified StaffDaffy Duck1REG PAY$2,386.93
7Certified StaffDaffy Duck1REG PAY$2,635.16

<tbody>
</tbody><colgroup><col><col><col><col span="3"></colgroup>
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Referencing tables in formula
2
Replies
13
Views
383
Peter_SSs
Peter_SSs
E
  • Question
Help with complex formula
Replies
1
Views
171
Fluff
Fluff
P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
156
StephenCrump
S
Dan Wilson
  • Solved
Help with a Run-Time Error
Replies
4
Views
221
dreid1011
dreid1011
E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
338
KRice
K

Forum statistics

Threads
1,214,832
Messages
6,121,845
Members
449,051
Latest member
excelquestion515

Share this page

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