# Need help with a lookup type formula

#### kcleere

##### Active Member
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### erik.van.geit

##### MrExcel MVP
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
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...

#### erik.van.geit

##### MrExcel MVP
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

Replies
1
Views
475
Replies
0
Views
230
Replies
0
Views
548
Replies
4
Views
148
Replies
28
Views
748

1,171,171
Messages
5,874,160
Members
433,030
Latest member
nmiller770678

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

### Which adblocker are you using?

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

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