Because of the way VLOOKUP works, it has to search the first column of
the given range. In your case, I would name my two sets of columns as
TableL (range B2:D5) and TableR (range E2:G5). Then use ISERROR to see
if my lookup value is contained in each table... such as:

=IF(ISERROR(VLOOKUP(A1,TableL,2,0)),VLOOKUP(A1,Tab leR,2)&",
"&VLOOKUP(A1,TableR,3),VLOOKUP(A1,TableL,2)&", "&VLOOKUP(A1,TableL,3))

in your example, checking table L for "f" would return an error
(because it is not found in that table), it would then look in table R
(ISERROR=TRUE) and return the values from columns 2 and 3 of that range
(in your case 10,14).

notice the use of concatenation to draw both results into one cell per
your example (namely 10, 14)

If A1 contains 'b', (ISERROR= FALSE) the formula would return "2, 6"

Does this work for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=376745