alteryx Community

# Alteryx Designer Desktop Discussions

## Is it possible to search in a range of columns?

6 - Meteoroid

Hi,

I'm trying to work out an Excel IFERROR formula in Alteryx:

=IFERROR(IF(AND(H2="",OR(RIGHT(LEFT(F2,4),1)="-",RIGHT(LEFT(F2,4),1)=""),LEFT(F2,2)="00"),

VLOOKUP(CONCATENATE(RIGHT(LEFT(F2,3),1),LEFT(G2,6)),M:Q,4,FALSE),IF(AND(H2="",OR(RIGHT(LEFT(F2,4),1)="-",RIGHT(LEFT(F2,4),1)=" "),LEFT(F2,2)<>"00"),VLOOKUP(CONCATENATE(LEFT(F2,3),LEFT(G2,6)),M:Q,4,FALSE),IF(AND(H2<>"",OR(RIGHT(LEFT(F2,4),1)="-",RIGHT(LEFT(F2,4),1)=" "),LEFT(F2,2)<>"00"),VLOOKUP(CONCATENATE(LEFT(F2,3),H2,G2),N:Q,3,FALSE),IF(AND(H2<>"",OR(RIGHT(LEFT(F2,4),1)=" ",RIGHT(LEFT(F2,4),1)="-"),LEFT(F2,2)="00"),VLOOKUP(CONCATENATE(RIGHT(LEFT(F2,3),1),H2,G2),N:Q,3,FALSE),IF((RIGHT(LEFT(F2,4),1)<>"-"),VLOOKUP(CONCATENATE(LEFT(F2,4),G2),O:Q,2,FALSE)))))),"")

I'm using the formula tool, and curious to know if a RANGE is possible. For example, in the IFERROR above, M:Q,4,False, (Column M through Column Q, return value in Column 4, False for exact match argument).

3 REPLIES 3
13 - Pulsar

Kindly consider using the Multi-Field Formula tool.

-Go to Help --> Sample Workflows --> Learn one tool at a time --> Preparation --> Multi-Field Formula.

-You can use the tool to select the range of columns you need, and then use the _CurrentField_ to apply logic to all those columns using a single expression.

I hope you find this helpful - Cheers!

6 - Meteoroid

Thanks, @RobertOdera I will look into that

13 - Pulsar

You are most welcome @ThePickleWizard

Labels