Can VLOOKUP ignore or skip certain data?

**xrajncajnx** · 07-17-2014, 02:52 PM

Please Login or Register  to view this content.

The column I am trying to return data from sometimes has multiple instances of the same reference number in column A. In these cases one or more can/will be an "Initial Reject" and then only one will be either "Approved" or "Rejected." I'd like the formula to skip over the "Initial Rejects" and find where the reference number is either "Approved" or "Rejected."

So in this case you have Item number 12345 on sheet 1 and on Sheet 2:

12345 | Initial Reject | 5/5/14
12345 | Initial Reject | 7/2/14
12345 | Rejected.......| 10/12/14

The formula should skip over the first two and return "Rejected"

**vivek123** · 07-17-2014, 05:01 PM

Can you post a sample file? It can be done by INDEX function.

**Haseeb Avarakkan** · 07-17-2014, 07:26 PM

You can use an IF formula to check column are 'initial reject' then skip those,

IF('Sheet 2'!I1:I1000<>"initial Reject",'Sheet 2'!A1:I1000)

the use VLOOKUP

=VLOOKUP(B148,IF('Sheet 2'!I1:I1000<>"initial Reject",'Sheet 2'!A1:I1000),9,0)

Or INDEX

=INDEX('Sheet 2'!I1:I1000,MATCH(1,IF('Sheet 2'!A1:A1000=B148,IF('Sheet 2'!I1:I1000<>"initial Reject",1)),0))

Both are Array Formula, so must be confirmed with CTRL+SHIFT+ENTER rather than just ENTER

If always will have only two outcomes, Rejected & Approved, then you can use simply

=LOOKUP(2,1/COUNTIFS('Sheet 2'!A:A,B148,'Sheet 2'!I:I,{"Rejected","Approved"}),{"Rejected","Approved"})

**Tony Valko** · 07-17-2014, 07:33 PM

Try this...

Data Range

	A	B	C	D	E	F
1	------	------	------	------	------	------
2	12345	Initial Reject	5/5/2014		12345	Rejected
3	12345	Initial Reject	7/2/2014
4	12345	Rejected	10/12/2014
5

This array formula** entered in F2:

=INDEX(B2:B10,MATCH(E2,IF(B2:B10<>"Initial Reject",A2:A10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**Vikas_Gautam** · 07-17-2014, 10:16 PM

try this..

An improvement over tony volko's formula..

Don't forget to click *

**Tony Valko** · 07-17-2014, 11:01 PM

Originally Posted by Vikas_Gautam

An improvement over tony volko's formula..

You're formula assumes "Rejected" will be the lookup value when the lookup value is a combination of lookup values, the item number and either "Rejected" or "Approved" .

Instead of looking up an OR condition we can simplify if by looking up an "is not" condition.

**Vikas_Gautam** · 07-17-2014, 11:11 PM

Corrected the solution..
Thanks, Tony Volko To pointing it out..
Check the attachment..

Don't forget to click *

**Tony Valko** · 07-18-2014, 08:26 AM

I think you're not understanding what is required.

The OP wants to lookup an item and return the corresponding status that is not "Initial Reject".

Data Range

	A	B	C	D	E	F
1	Item	Status	Date	-----	Item	Status
2	11111	Initial Reject	5/5/2014		22222	Approved
3	11111	Initial Reject	2/7/2014
4	11111	Rejected	12/10/2014
5	22222	Approved	5/6/2014
6	22222	Initial Reject	2/25/2014
7	33333	Rejected	7/1/2014
8	44444	Initial Reject	2/15/2014
9	44444	Approved	2/16/2014
10	44444	Initial Reject	2/14/2014

We're looking for Item 22222 and the corresponding status that is not Initial Reject.

This array formula** entered in F2:

=INDEX(B2:B10,MATCH(E2,IF(B2:B10<>"Initial Reject",A2:A10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**xrajncajnx** · 07-18-2014, 10:47 AM

Quoted wrong post by mistake

**xrajncajnx** · 07-18-2014, 11:17 AM

Originally Posted by Tony Valko

I think you're not understanding what is required.

The OP wants to lookup an item and return the corresponding status that is not "Initial Reject".

=INDEX(B2:B10,MATCH(E2,IF(B2:B10<>"Initial Reject",A2:A10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Sorry, replied to the wrong post...

Your formula works for what I need, however, when I copy the formula down the column it picks up the existing "Initial Rejects" until I go into those particular cells and reenter the formula using the CTRL,SHIFT,ENTER. Is there a way to copy the formula down while applying that method of entering for the entire column or will I need to do that with each individual cell?

**Tony Valko** · 07-18-2014, 02:18 PM

If you're copying the formula down a column then we need to make some of the references row absolute:

Data Range

	A	B	C	D	E	F
1	Item	Status	Date	-----	Item	Status
2	11111	Initial Reject	5/5/2014		11111	Rejected
3	11111	Initial Reject	2/7/2014		22222	Approved
4	11111	Rejected	12/10/2014		33333	Rejected
5	22222	Approved	5/6/2014		44444	Approved
6	22222	Initial Reject	2/25/2014
7	33333	Rejected	7/1/2014
8	44444	Initial Reject	2/15/2014
9	44444	Approved	2/16/2014
10	44444	Initial Reject	2/14/2014

This array formula** entered in F2 and copied down:

=INDEX(B$2:B$10,MATCH(E2,IF(B$2:B$10<>"Initial Reject",A$2:A$10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**xrajncajnx** · 07-29-2014, 11:21 AM

Originally Posted by Tony Valko

If you're copying the formula down a column then we need to make some of the references row absolute:

Data Range

	A	B	C	D	E	F
1	Item	Status	Date	-----	Item	Status
2	11111	Initial Reject	5/5/2014		11111	Rejected
3	11111	Initial Reject	2/7/2014		22222	Approved
4	11111	Rejected	12/10/2014		33333	Rejected
5	22222	Approved	5/6/2014		44444	Approved
6	22222	Initial Reject	2/25/2014
7	33333	Rejected	7/1/2014
8	44444	Initial Reject	2/15/2014
9	44444	Approved	2/16/2014
10	44444	Initial Reject	2/14/2014

This array formula** entered in F2 and copied down:

=INDEX(B$2:B$10,MATCH(E2,IF(B$2:B$10<>"Initial Reject",A$2:A$10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Is it possible to copy this formula down a table?

Here is the formula that I've used:

Please Login or Register  to view this content.

**vando-kun** · 03-01-2018, 02:48 AM

Hi Tony,

I have used your formula since it fits to my need. But our different from OP is that I have two more data that needs to be excluded in the look up, I've been trying different approach but can't get it done. My understanding is that I only need to tweak somewhere in this line if it's based on OP's post *IF(B2:B10<>"Initial Reject",A2:A10)*. Can you help me add the two more data that needs to be excluded? Thank you.

**FDibbins** · 03-01-2018, 03:08 AM

vando-kun welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Can VLOOKUP ignore or skip certain data?

LinkBack

Thread Tools

Rate This Thread

Display

Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Re: Can VLOOKUP ignore or skip certain data?

Thread Information

Users Browsing this Thread

Similar Threads

Vlookup if blank skip to next value loop

VBA ignore or skip worksheets if not available

Excel 2007 : skip blank cells in vlookup

Vlookup using skip column

VBA Code Vlookup Skip Cell if no new information

Bookmarks

Bookmarks

Posting Permissions