Sir Valko,
i've tried the formula and make a scenario on column f and g

if i try to place the same lookup value on f30 and also the same lookup
value on g20...
the result will come from h20 and it will disregard the other result from h30.

in my formula, under the same scenario, i bear the h30 result...
maybe the OP is interested about priorities between column or row, firstly...
i guess he likes the column to be prioritized...maybe i have misunderstood
the look_up sequencing...

regards,
driller
--
*****
birds of the same feather flock together..



"T. Valko" wrote:

Try this array formula** :

=IF(COUNTIF(Sheet2!B2:C10,B2),INDEX(Sheet2!D2:D10, MATCH(TRUE,ISNUMBER(SEARCH(B2,Sheet2!B2:B10&"_"&Sh eet2!C2:C10)),0)),IF(COUNTIF(Sheet2!F2:G10,B2),IND EX(Sheet2!H2:H10,MATCH(TRUE,ISNUMBER(SEARCH(B2,She et2!F2:F10&"_"&Sheet2!G2:G10)),0)),""))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Ram" wrote in message
...
The data to be returned is Text.

thanks

"T. Valko" wrote:

Also, is the value to be returned a number or text?

Biff

"T. Valko" wrote in message
...
Do you mean that the lookup_value could be in either columns B&C or F&G
or
none?

It's not just in either column B or column F?

Biff

"Ram" wrote in message
...
Hi,

I'll give you an idea of the file that i am working on

Sheet 1:
Cell B2 has numeric data ex: 39022

Sheet 2:
Column B, C & D; F,G & H have data upto 50 rows

Destination cell for the formula is Sheet1!B3

What i need the formula in Sheet!B3 to do:

search for the value from Sheet1!B2 in Sheet2!B:C and if value exists
then
return value from column D;
if value does not exist in B or D, then search in columns F & G, if
exists
then return value from H.

If value does not exist in either B:C and F:G, then return blank

It has been very challenging for me to solve this. I manage to look
up
using the look up function (vector), however i couldnt figure out, how
to
integrate the condition.

Please let me know if you require more input from me.

regards,