Sir Valko,
maybe you can share later, here, your test results from the file received
from Ram, i'm interested to know how the formulas work for either scenario...

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



"T. Valko" wrote:

I'll put something together later on tonight.

Biff

"driller" wrote in message
...
Sir Valko,
maybe its true,
but my sample sheet looks correct after some test wherein lookup_value is
a
number...lookup_result is a text or number...
maybe u can share me your test data...

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



"T. Valko" wrote:

Your formula doesn't work if the lookup_value is in column F.

Biff

"driller" wrote in message
...
may be we can start with this one...
Sheet 1:
Cell B2 has numeric data ex: 39022

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

'=IF(COUNTIF(Sheet2!B1:C50,B2)0,INDEX((Sheet2!B1: D50),IF(NOT(ISNA(MATCH(B2,Sheet2!B1:B50,0))),MATCH (B2,Sheet2!B1:B50,0),IF(NOT(ISNA(MATCH(B2,Sheet2!C 1:C50,0))),MATCH(B2,Sheet2!C1:C50,0),#N/A)),3),IF(COUNTIF(Sheet2!G1:G50,B2)0,INDEX((Sheet 2!F1:H50),IF(NOT(ISNA(MATCH(B2,Sheet2!F1:F50,0))), MATCH(B2,Sheet2!F1:F50,0),IF(NOT(ISNA(MATCH(B2,She et2!G1:G50,0))),MATCH(B2,Sheet2!G1:G50,0),#N/A)),3),"NOT
found"))

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



"Ram" wrote:

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,