# If cell contains text, lookup two cells above and return value.

Status
Not open for further replies.

#### Drewmyster

##### Board Regular
Hard to explain but the requirement is that if A1:A5 contains any value (only one cell will contain a value at any one time), then look up and return the value of the cell two rows below that value.

For example, If A3 contains the number 5, then look up C3 which contains the word "Month" and return the text string there.

Also

If A2 contains the number 2, then look up C2 which contains the word "Week" and return that word.

Any help appreciated.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Fluff

##### MrExcel MVP, Moderator
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
=LOOKUP(2,1/(A1:A5<>""),C1:C5)

#### Drewmyster

##### Board Regular
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
=LOOKUP(2,1/(A1:A5<>""),C1:C5)
This does not work for me, perhaps a variation of this formula?

#### Fluff

##### MrExcel MVP, Moderator
In that case you need to be more explicit about what you want. Your title says to lookup two rows above, your question says two rows below, but then you say to return a value in col C which is on the same row, but two columns to the right.

#### Drewmyster

##### Board Regular
Hello again,

I apologise for being vague, this and the other thread you responded to today are the same query (I didn’t realise I had previously submitted the question on here).

So the explain further…

The 4 cells in Row 1 are labelled “day”, “week”, “month“ and “year”.

The 4 cells in Row 3 are numerical, but only for one cell at any time.

When Row 3 contains a number, I need the formula to return the label two cells above the cell with the number from Row 1 (e.g. ”week”).

Does this help?

#### Fluff

##### MrExcel MVP, Moderator
In that case I will close this thread & we can continue in the other one.

Status
Not open for further replies.

Replies
5
Views
252
Replies
4
Views
482
Replies
3
Views
289
Replies
3
Views
264
Replies
3
Views
120

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,782
Messages
5,766,433
Members
425,354
Latest member
kshivanand21

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