lookup for a value in multiple columns and return a result

[Ram]

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,

 

[T. Valko]

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]

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]

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

Biff

 

[Ram]

The data to be returned is Text.

thanks

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

Biff

 

[driller]

may be we can start with this one...
'=IF(COUNTIF(Sheet2!B1:C50,B2)>0,INDEX((Sheet2!B150),IF(NOT(ISNA(MATCH(B2,Sheet2!B1:B50,0))),MATCH(B2,Sheet2!B1:B50,0),IF(NOT(ISNA(MATCH(B2,Sheet2!C1:C50,0))),MATCH(B2,Sheet2!C1:C50,0),#N/A)),3),IF(COUNTIF(Sheet2!G1:G50,B2)>0,INDEX((Sheet2!F1:H50),IF(NOT(ISNA(MATCH(B2,Sheet2!F1:F50,0))),MATCH(B2,Sheet2!F1:F50,0),IF(NOT(ISNA(MATCH(B2,Sheet2!G1:G50,0))),MATCH(B2,Sheet2!G1:G50,0),#N/A)),3),"NOT found"))

regards,
driller

 

[T. Valko]

Try this array formula** :

=IF(COUNTIF(Sheet2!B2:C10,B2),INDEX(Sheet2!D210,MATCH(TRUE,ISNUMBER(SEARCH(B2,Sheet2!B2:B10&"_"&Sheet2!C2:C10)),0)),IF(COUNTIF(Sheet2!F2:G10,B2),INDEX(Sheet2!H2:H10,MATCH(TRUE,ISNUMBER(SEARCH(B2,Sheet2!F2:F10&"_"&Sheet2!G2:G10)),0)),""))

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

Biff

 

[T. Valko]

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

Biff

 

[driller]

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

 

[driller]

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

 

[T. Valko]

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

 

[T. Valko]

I'll put something together later on tonight.

Biff

 

[Ram]

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]

Send it to me:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[T. Valko]

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

 

[driller]

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

 

[driller]

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