If(isna(vlookup function help so that it looks up the text in far right column

Hi

I moved your post to excel,you had it in an outlook forum

You can use vlookup to return a value in any column to the RIGHT of the column of the column you are searching in, by changing the column number. Th syntax is...

=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

=vlookup(A1,B1:D20,3,false) will search in column D
=vlookup(A1,B1:D20,2,false) will search in column C
=vlookup(A1,B1:D20,1,false) will search in column B

Alternatively you could use index/match combo, then you dont need to specify the column, the formula will search for it

If you're using Excel 2007 (or later) use the IFERROR function instead of ISNA.

In the VLOOKUP function you can specify which column number in the table_array to get the result.

For example:

=IFERROR(VLOOKUP(A1,X:Z,2,0),"")

That formula will look in column X for the lookup_value and return the corresponding value from the 2nd column of the table_array. The 2nd column of the table_array is column Y.

Maybe this...

Cell A14 contains the lookup_value M14.

Enter this formula in B14 and copy across to C14:

=IFERROR(VLOOKUP($A14,$A$7:$C$9,COLUMNS($B14:B14)+1,0),"")

You can do a match on the far right column and the use that with Index to get the items, as suggested.

=INDEX($A$7:$C$9,MATCH("Unassigned 1",$C$7:$C$9,0),1)
=INDEX($A$7:$C$9,MATCH("Unassigned 1",$C$7:$C$9,0),2)
=INDEX($A$7:$C$9,MATCH("Unassigned 1",$C$7:$C$9,0),3)

Add error trapping formula if unassigned 1 is not always present.

BTW. Vlookup works be locating a match in the first column of the array