How to search in multiple columns in google sheets?

Question

I have several columns as follows:

Nugegoda  Branch    Nugegoda Branch REF     Malabe Branch   Malabe Branch REF   
102                 2222                    103             33333   
201                 2222                    203             33333   
301                 2222                    303             33333   
401                 2222                    403             33333   
501                 2222                    503             33333   
                

I have named those ranges as Nugegoda and Malabe. I want to write a VLOOKUP for key 103 searching in both of these ranges together, how can I do that?

Answer 1

You can also try

=IFERROR (VLOOKUP(103, {$B$2:$C; $E$2:$F; $H$2:$I}, 2, FALSE))

or

 =IFERROR (VLOOKUP(L1, {$B$2:$C; $E$2:$F; $H$2:$I}, 2, FALSE))

Answer 2

Try this:

=iferror(VLOOKUP($L$1,$B$2:$C,2,False),VLOOKUP($L$1,$E$2:$F,2,FALSE))

or if you don't want to extra cell reference (L1) use this directly:

=iferror(VLOOKUP(103,$B$2:$C,2,False),VLOOKUP(103,$E$2:$F,2,FALSE))

Essentially you use an if condition with VLOOKUP to check if the value exists in the first range, if not (error) then look for the other range.

Update based on your comment.

If you want to look for more than 2 ranges then you can take advantage of the ISNUMBER() function. The following searches for B2:B, E2:E and H2:H.

=IF(ISNUMBER(VLOOKUP(103,$B$2:$C,2,False)),VLOOKUP(103,$B$2:$C,2,False),IF(ISNUMBER(VLOOKUP(103,$E$2:$F,2,FALSE)),VLOOKUP(103,$E$2:$F,2,FALSE),IF(ISNUMBER(VLOOKUP(103,$H$2:$I,2,FALSE)),VLOOKUP(103,$H$2:$I,2,FALSE))))