Lookup value in multiple columns, and return a STRING of highest priority column

[lamensterms]

Hey guys,

I'm trying to search a value in multiple columns, and return a STRING if this value is found. The STRING depends on which column the value is found in.

I have 2 work sheets:
Sheet 1 = 'Items'
Sheet 2 = 'Location

On sheet 'Items', I have a list of values in Columns A. Some of these values are also listed on my second sheet 'Location', in columns A, B or C (not all values from 'Items' will be listed in 'Locations's columns), and some values will be listed in multiple columns on Sheet 2).

On Sheet 1 'Items', I would like Column B to search Sheet 2, and display a STRING/TEXT if the value is found. But the locations have a hierarchy. I wish to display the highest 'priority' Location that each Item is found in. Column C is the highest priority.

Sorry for the sloppy description, hopefully someone knows what I am trying to do.

I have experimented with nested IF and MATCH functions.

=IFERROR(IF(MATCH(A1,Location!C:C,0),"Installed"),IF(MATCH(A1,Location!B:B,0),"Delivered"),IF(MATCH(A1,Location!A:A,0),"Factory"),"")

...but I seem to be making an error somewhere (probably a simple syntax error).

Thanks for any help

 

[Marcelo Branco]

I think you need three IFERROR - something like (not tested)

=IFERROR(1st formula,IFERROR(2nd formula,IFERROR(3rd formula,"")))

M.

 

[lamensterms]

Perfect, thanks so much for the reply Marcelo.. you were spot on.

Working formula:
=IFERROR(IF(MATCH(A1,Location!C:C,0),"Installed"),IFERROR(IF(MATCH(A1,Location!B:B,0),"Delivered"),IFERROR(IF(MATCH(A1,Location!A:A,0),"Factory"),"")))

Thanks again!

 

[Marcelo Branco]

You are welcome. Glad to help

M.