lookup for a value in multiple columns and return a result

R

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

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
 
R

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.
 
D

driller

may be we can start with this one...
'=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!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

T. Valko

Try this array formula** :

=IF(COUNTIF(Sheet2!B2:C10,B2),INDEX(Sheet2!D2:D10,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
 
D

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
 
D

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

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
 
R

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

T. Valko

Send it to me:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff
 
T

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
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top