IF Formula Change

[Mike Guest98]

Hi

I’m using the following formula but would like to change it to include more than one result:


=IF(ISERROR(LOOKUP(2,1/(E36:E52<>""),E25:E50)),"",LOOKUP(2,1/(E25:E50<>""),E25:E50))


If for example the existing formula was placed in cell B5 but an additional two results could be added (if found) and the results would be placed in the adjacent cells of B6 and B7. I’ve tried and tried with no luck.

<style type="text/css">p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; }p.western { font-family: "Times New Roman", serif; font-size: 12pt; }p.cjk { font-family: "Times New Roman", serif; font-size: 12pt; }p.ctl { font-family: "Times New Roman", serif; font-size: 12pt; }</style>

 

[Special-K99]

Formulas cant place values in other cells only VBA can do that.
You need to put formulas in B6 and B7

What are your additional values?

 

[Mike Guest98]

So if I break up the formula and put the following in cell B5 to say =IF(ISERROR(LOOKUP(2,1/(E25:E35<>""),E25:E35)),"",LOOKUP(2,1/(E25:E35<>""),E25:E35)) and then =IF(ISERROR(LOOKUP(2,1/(E46:E52<>""),E46:E52)),"",LOOKUP(2,1/(E46:E52<>"") in cell B6 I would get results for two cells. I could break it up more for 3 cells.

I'm not sure what you're referring to with additional values.
Thanks

 

[Mike Guest98]

Can I also ask if there is a way for the formula have an error if more than one result is found.
Thank-you

 

[Special-K99]

Cant you just shorten that to

=IFERROR(LOOKUP(2,1/(E25:E35<>""),E25:E35)),"")

 

[Special-K99]

Im not sure what youre asking for in your original request.

Can you provide some output?

 

[Mike Guest98]

That works, thanks. Is there a way to indicate an error if more than 1 result is found?
Thanks

 

[Special-K99]

LOOKUP only returns one result, you'd need to use some other formula to pick up more than one result.

Usually takes the form of an array formula like

'{=IFERROR(INDEX($A$1:$A$5,SMALL(IF(($A$1:$A$5<>""),ROW($A$1:$A$5)),ROW(A1))-(ROW(A$1)-1),1),"")}

or non array

'=IFERROR(INDEX(A$3:A$100,AGGREGATE(15,6,ROW(A$3:A$100)/(A$3:A$100<>""),ROWS(A$3:A3))-(3-1),1),"")

Dont use them formulas, they wont work, they're just templates.

 

[Mike Guest98]

I will try this tonight and get back to you.

Thank-you for the assistance.

 

[Special-K99]

Those formulas are usually used to remove unwanted cells thereby producing a list, a common problem is taking empty cells out of a column, hence the <>""
If you google for something like excel remove empty cells list you'll see lots of examples as it's a standard solution.
You'd need to change the condition.

'{=IFERROR(INDEX($A$1:$A$5,SMALL(IF(($A$1:$A$5<>""),ROW($A$1:$A$5)),ROW(A1))-(ROW(A$1)-1),1),"")}

or non array

'=IFERROR(INDEX(A$3:A$100,AGGREGATE(15,6,ROW(A$3:A$100)/(A$3:A$100<>""),ROWS(A$3:A3))-(3-1),1),"")

Red = Original List (range can be expanded to other columns to return specific column as per orange below - range in the condition shouldnt be expanded as you're only checking for one cell in a column)
Blue = Condition - Use (condition1)*(condition2) for AND (condition1)+(condition2) for OR - normal AND() OR() doesnt work
Green = First line of list
Orange = Column to return from the original list