# Need formula that will return a value from a range with blank cells

#### chameleon_crp

##### New Member
Is there a formula that will tell me what was the last text in a range that has blank cells? All the cells have formulas and the blank cells have formulas with the outcome of "".
Example:

Column A
row 1
row 2 X
row 3 X
row 4
row 5
row 6

column b
row 1 A
row 2
row 3
row 4
row 5
row 6 A

The formula i am looking for would look up the range A1:B6 and look up the last text before the A in column B which should be an X from column A.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Not altogether clear to me how complicated this really is? If you're saying you need to find the first non-null string in Col A that occurs between the first and last "A" reference in Col B and where these "A" references can themselves move ... then perhaps:

Code:
``=LOOKUP(2,1/((A1:A6<>"")*(ROW(A1:A6)>MATCH("A",B1:B6,0))*(ROW(A1:A6)< MATCH(2,INDEX(1/(B1:B6="A"),0)))),A1:A6)``

but I suspect this may be a little brute force for your requirements? Can you elaborate further regards requisite flexibility?

This gives the last value that is not a blank (i.e., "")...
Rich (BB code):
``````=LOOKUP(9.99999999999999E+307,
SEARCH("?",A1:INDEX(A:A,MATCH(REPT("z",255),A:A))),
A1:INDEX(A:A,MATCH(REPT("z",255),A:A)))``````
This can be easily adapted to column B...
Rich (BB code):
``````=LOOKUP(9.99999999999999E+307,
SEARCH("?",B1:INDEX(B:B,MATCH(REPT("z",255),B:B))),
B1:INDEX(B:B,MATCH(REPT("z",255),B:B)))``````

If you want the last non-blank column A-value that occurs before the non-blank column B-value (not sure this is what is wanted)...

F1, control+shift+enter, not just enter:
Rich (BB code):
``````=MATCH(9.99999999999999E+307,
SEARCH("?",B1:INDEX(B:B,MATCH(REPT("z",255),B:B))))-1
``````
F2, just enter:
Rich (BB code):
``````=LOOKUP(9.99999999999999E+307,
SEARCH("?",A1:INDEX(A:A,F1)),A1:INDEX(A:A,F1))``````

Replies
16
Views
727
Replies
1
Views
128
Replies
16
Views
259
Replies
2
Views
112
Replies
8
Views
503

1,207,094
Messages
6,076,547
Members
446,212
Latest member
KJAYPAL200

### 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?

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