Need help with a lookup type formula

[kcleere]

I have a requirement to be able to enter a formula in cell B1 that looks at a value in A1 and then looks up that value in a column range and IF found anywhere in that column range, enter the the vaue from $A$6.

This formula would be copied down x number of rows in column B but the lookup column range and $A$6 would remain the same.

I hope this makes sense and thank you in advance for any suggestions.

Ken

 

[erik.van.geit]

Hi, Ken,
Welcome to the BOARD !!!!!

try this
  A        B        C   
1 AAA      value A6 AAA 
2 BBB               DDD 
3 CCC                   
4 DDD      value A6     
5                       
6 value A6              

test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B1:B4 =IF(ISNA(MATCH(A1,C:C,0)),"",$A$6)

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[kcleere]

Need further help on a lookup formula - Criteria Updated

I'm afraid I stated my criteria completely wrong.

What I now know I need is to enter a formula in B1 that looks at a value in A1 and takes that value and looks it up in another column range.

If it finds that value in that column range AND finds an "entry" in a cell on the same row where it found the value in the column range THEN it would return the value from cell $A$6.

If it finds the value in the column and there is no value in the cell on the same row it returns 0. Conversly, if it does not find the value in the column it returns 0.

Whew...this one is eating my lunch...

Thank you in advance for your suggestions

 

[erik.van.geit]

please provide an example

 

[erik.van.geit]

in response to your email (sending example)

there are tools to provide examples and even if you couldn't use them: posting some simple data shouldn't be that difficult

check out colo's htmlMaker (see link at bottom page)
&
"Table-It" (see my signature)

for this kinda question the second would be good