lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

Hi, thats correct.

Using lookup function i could only look up in B&C but couldnt give a
condition that if not found in B&C, then look in F&G.

"T. Valko" wrote:

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

Yes, my formula uses MATCH and concatenates the columns into a single
lookup_array. Therefore, MATCH will only find the first instance. Sort of
like this:

102_39022
39022_105

The first instance match is 102_39022

In your formula, you're going through each individual column one at a time.

102.....39022
39022.....105

So your match would be 39022.....105 because it's in the leftmost column
which gets checked first.

Biff

"driller" wrote in message
...
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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

hi Valko & Driller,

How do i send you both the actual file, so that we can work on real data and
remove any ambiguity, as i believe i have not correctly worded my problem.

I really appreciate that you both are willing to assist me with my query.
thanks a lot.

regards,
ram

"T. Valko" wrote:

Yes, my formula uses MATCH and concatenates the columns into a single
lookup_array. Therefore, MATCH will only find the first instance. Sort of
like this:

102_39022
39022_105

The first instance match is 102_39022

In your formula, you're going through each individual column one at a time.

102.....39022
39022.....105

So your match would be 39022.....105 because it's in the leftmost column
which gets checked first.

Biff

"driller" wrote in message
...
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,

lookup for a value in multiple columns and return a result
 

Send it to me:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Ram" wrote in message
...
hi Valko & Driller,

How do i send you both the actual file, so that we can work on real data
and
remove any ambiguity, as i believe i have not correctly worded my problem.

I really appreciate that you both are willing to assist me with my query.
thanks a lot.

regards,
ram

"T. Valko" wrote:

Yes, my formula uses MATCH and concatenates the columns into a single
lookup_array. Therefore, MATCH will only find the first instance. Sort of
like this:

102_39022
39022_105

The first instance match is 102_39022

In your formula, you're going through each individual column one at a
time.

102.....39022
39022.....105

So your match would be 39022.....105 because it's in the leftmost column
which gets checked first.

Biff

"driller" wrote in message
...
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,

lookup for a value in multiple columns and return a result
 

OK, I got the file.

I'll take a closer look at it tomorrow. It's getting late where I'm at (3:00
AM).

Biff

"T. Valko" wrote in message
...
Send it to me:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Ram" wrote in message
...
hi Valko & Driller,

How do i send you both the actual file, so that we can work on real data
and
remove any ambiguity, as i believe i have not correctly worded my
problem.

I really appreciate that you both are willing to assist me with my query.
thanks a lot.

regards,
ram

"T. Valko" wrote:

Yes, my formula uses MATCH and concatenates the columns into a single
lookup_array. Therefore, MATCH will only find the first instance. Sort
of
like this:

102_39022
39022_105

The first instance match is 102_39022

In your formula, you're going through each individual column one at a
time.

102.....39022
39022.....105

So your match would be 39022.....105 because it's in the leftmost column
which gets checked first.

Biff

"driller" wrote in message
...
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,

lookup for a value in multiple columns and return a result
 

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,

lookup for a value in multiple columns and return a result
 

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,