To return the value of the cell in a row if it match the condition

**richard.77** · 04-20-2016, 05:05 AM

Hi,

Below is an extraction of my data and I am trying to find a formula that will return me the value of the cell if the last number is 1. So in below example, i should be getting "1|1|4|1" returned since it matches my condition.

4|3|2|3 3|2|5|4 1|1|4|1 2|4|3|2 5|4|1|5

Could someone help me out on this?

Thanks and have a good day!

**Kevin#** · 04-20-2016, 05:19 AM

welcome to the forum
What are those values in post#1?
Is each one a cell value?
The last number I see is 5, so how is your condition met?

**NeedForExcel** · 04-20-2016, 05:21 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

**richard.77** · 04-20-2016, 05:33 AM

I'm sorry. The formatting got removed somehow. There is actually a cluster of values in each cell. To illustrate, each line below refer to a cell by itself. Is there a way to have "1|1|4|1" returned since it contains 1 at the right?

4|3|2|3
3|2|5|4
1|1|4|1
2|4|3|2
5|4|1|5

**NeedForExcel** · 04-20-2016, 05:38 AM

Put this in G1 & Drag Across

=MAX(IF($D$1:$D$5=1,A$1:A$5,""))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

But what if there are multiple rows that end with 1?

**Khalidngo** · 04-20-2016, 05:40 AM

Hi Richard,

Check this:

=LOOKUP(2,1/(RIGHT($A$2:$A$6)+0=1),$A$2:$A$6)

Blessing

**Kevin#** · 04-20-2016, 06:38 AM

My contribution:

With values in columnA starting at A2, paste this into B2 and drag down

Formula:

Please Login or Register  to view this content.

**vishal0589** · 04-20-2016, 07:30 AM

My Contribution:

=INDEX($A$2:$A$6,MATCH(1,MMULT(VALUE(RIGHT($A$2:$A$6,1)),1),0),1)

Thanks & Regards,
CMA Vishal Srivastava

**richard.77** · 04-20-2016, 09:13 PM

Thanks for all the contributions. However, i am getting #VALUE!, #REF, etc.

I have uploaded an extract of my dataset for your assistance.

The data is in rows and there can only be a "1" at the right end for each row.

I have highlighted what i wish to be returned in the formula.

Thanks in advance.

**FlameRetired** · 04-20-2016, 11:09 PM

Try in A2

Formula:

Please Login or Register  to view this content.

and fill down.

**richard.77** · 04-21-2016, 12:23 AM

Thanks Dave. It works initially!

However, when I drag the formula down, it display the value of the first cell I dragged although the formula is correct.

I have to hit enter at each row of the formula column for the value to be updated.

**Khalidngo** · 04-21-2016, 12:40 AM

Hi Richard,

Try this {array formula} and copy down and across:

=IFERROR(INDEX(B$2:B$5,SMALL(IF(RIGHT(B$2:B$5,2)="|1",ROW($B$2:$B$5)-1),ROW(A1))),"")

Array formula needs to be entered with Ctrl+Shift+Enter (not just enter)

Blessing

**FlameRetired** · 04-21-2016, 12:54 AM

Originally Posted by richard.77

Thanks Dave. It works initially!

However, when I drag the formula down, it display the value of the first cell I dragged although the formula is correct.

I have to hit enter at each row of the formula column for the value to be updated.

Is there any chance you have Calculation set to Manual?

**vishal0589** · 04-21-2016, 01:46 AM

Hi Richard,
Try this non CSE formula and drag it down:

=LOOKUP(2,1/SEARCH("1",RIGHT(B2:U2)),B2:U2)

Thanks & Regards,
CMA Vishal Srivastava

**richard.77** · 04-21-2016, 04:34 AM

Thanks Vishal and Dave. Both worked. Problem solved.

Thanks all.

This is a great community!

**FlameRetired** · 04-21-2016, 04:12 PM

Glad to hear it. You're welcome and thanks for the feedback.

To return the value of the cell in a row if it match the condition

LinkBack

Thread Tools

Rate This Thread

Display

To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Re: To return the value of the cell in a row if it match the condition

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Search column for condition(s) and return "1" if match

[SOLVED] Match string between cells in two tables and return a value based on condition

Return a cell value from different sheet if the first condition is met

[SOLVED] Lookup in columns and return if the condition is match

2010 - Need to match data and return specific cell value - SUMIF? MATCH? INDEX? HELP!

Many Condition need to match and return a unit price - Urgent assignment

Match two condition and return a value

Bookmarks

Bookmarks

Posting Permissions