Find furthest right cell with IF statement and get cell value in same column?

[excellenthelp]

Hi Excellers,

How do I write a formula that will spit out the cell value in any cell in the same column with the furthest right cell that has a value based on a IF statement?

Attached is a scenario workbook.

Thank you.

[AlKey]

Enter this array formula in B10 and copy down

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Or you can sue this non-array formula

Please Login or Register  to view this content.

[jewelsharma]

This should work if every cell in Row 2 up till the furthest cell is non-blank.

Please Login or Register  to view this content.

If cells in Row 2 may have blanks in between the start and furthest cell, try the following Array Formula:

Please Login or Register  to view this content.

Note: Array Formulas must be entered/confirmed with [Ctrl] + [Shift] + [Enter]

[excellenthelp]

That works perfectly for the workbook I have provided!

However, I was unable to duplicate this to the workbook where I want the formula to nest in. I have simplified the workbook with just two months. It has the same exact concept above but for each of the 12 months.

I want the forumla to nest in the sheet "Denomacion". You will see a mess of previous formula that worked for just January (Enero). It would not continue onto the other months. -___-

Here you go.

[excellenthelp]

I am bumping to further my inquire about the formula.

[jewelsharma]

I'm unable to follow your new sheet. are you able to exemplify it by showing which cells are working fine, which ones are not and how should they work.

[excellenthelp]

Thank you for your time, here is the explanation pulled from the workbook.

------

The goal is to fix the denomacion box work properly on the sheet 'Budget'.

The data is pulled from the sheet 'denomacion'.

On the sheet 'denomacion', lies the culprit. Its home is in column C while everything else is perfectly fine so the focus is on only column C.

How it should work:
- When I mark an 'x' in G7 on sheet 'Budget', column C on sheet 'denomacion' should then pull from the same column under G7 for each item such as ahorro, renta, alimentacion, etc. that has an 'x' next to it in column E

- However, when I move onto the next column to mark an 'x' in H7 on sheet 'Budget', column C on sheet 'denomacion' should now pull from the same column under H7 for each item with an 'x' next to it in column E. Same for S7 with an 'x' next to it in column Q and so on.

- This should continue all the way through to column EO, skipping the blanks in between and all other data, of course.


Hope this is clear enough. Please inquire if you need further clarification.

Thanks!

[excellenthelp]

Bumping again in hope of a response with assistance.

[JeteMc]

Try pasting this array formula* in Denomacion!C4:

Please Login or Register  to view this content.

You are going to get a #Value however if you'll press the F2 key and then press Ctrl, Shift and Enter simultaneously, It will work, as indicated by the appearance of brackets {} around the formula. At that point you can copy it down the column as far as desired. Here is a copy of your file with the formula applied and an x placed in column U:
Copy of SampleWBv1.xlsx
Let me know if you have any questions.

[excellenthelp]

Thank you very much, JeteMc!

The array formula provided does work well! I was dreading array formulas for a while because I know how complicated and problematic they can get.

Thanks again!

Marking this as SOLVED.

[JeteMc]

Your Welcome, thank you for the feedback and for marking the thread 'Solved'. I hope that you have a good day.