Need help with a lookup type formula

kcleere

Active Member
Joined
Oct 4, 2006
Messages
314
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

Active Member
Joined
Oct 4, 2006
Messages
314
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,136,348
Messages
5,675,241
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top