# V lookup and if statement

1. ## V lookup and if statement

Hello

Can someone help me with the following

I want a formula put in that shows if column d has the value p1 in it, add in the sector number

2. ## Re: V lookup and if statement

You can put this formula in E2:

=IF(RIGHT(D2,2)="P1","SECTOR="&A2,"")

then copy down.

Hope this helps.

Pete

3. ## Re: V lookup and if statement

You haven't said where you want the formula.

If I was inserting the formula in column E then the formula would be
Formula:
`Please Login or Register  to view this content.`

This formula searches D2 for data that is in B2. if it finds it it brings through the data in cell A2. If it doesn't find it the cells remains blank.

This will allow the formula to be copied down your sheet.

I think Pete's formula will only look for P1 and only look at the last 2 characters in cell D2 (so wouldn't find P10 or P11).

4. ## Re: V lookup and if statement

Try:
Formula:
`Please Login or Register  to view this content.`

5. ## Re: V lookup and if statement

@Declamatory,

That picks up P10 and P11.

Pete

6. ## Re: V lookup and if statement

My guess is that he wants to take the characters after the word "Site" and use them to look up the table in columns A:B. Not sure if he actually wants "SECTOR=" or if that was part of the explanation

7. ## Re: V lookup and if statement

Sorry Pete, it does but incorrectly. Cell E3 should have a result of nothing but it has a value of SECTOR=293291 because it is bringing through the Sector for attribute P10 when Cell D3 has an attribute of P1.

The formula doesn't take into account the attributes changing in column B such as P5

8. ## Re: V lookup and if statement

My guess is that he wants to take the characters after the word "Site" and use them to look up the table in columns A:B. Not sure if he actually wants "SECTOR=" or if that was part of the explanation
Then my formula would be
Formula:
`Please Login or Register  to view this content.`

9. ## Re: V lookup and if statement

@Declamatory@ your formula checks row by row and, effectively, matches the Attribute on that row. It's not looking up the table in columns A:B

Note that the original file has over a million "blank" rows, hence the size of the file. I have deleted them.

10. ## Re: V lookup and if statement

Ahh, I see TMS. Good job.

11. ## Re: V lookup and if statement

@Declamatory: thanks for the feedback and rep, much appreciated.

All we need now is for the OP to come back and let us know what he's really trying to do

12. ## Re: V lookup and if statement

Thanks for this. I cannot seem to get it working

13. ## Re: V lookup and if statement

The formula does not appear to be looking at the whole table

14. ## Re: V lookup and if statement

Re-post a file showing what is not working.

15. ## Re: V lookup and if statement

I need it to pick up the whole table with the correct numbers

16. ## Re: V lookup and if statement

You need to change the formula in E2 to this:

=INDEX(\$A:\$A,MATCH(RIGHT(\$D2,LEN(D2)-(FIND("=",\$D2,1))),\$B:\$B,0))

Use Ctrl-Shift-Enter to commit it, rather than the usual < Enter >, as it is an array formula, and then copy it down as required.

Hope this helps.

Pete

17. ## Re: V lookup and if statement

This works but it's NOT an array formula:

Formula:
`Please Login or Register  to view this content.`

It looks as though you have changed the text prior to the code.

18. ## Re: V lookup and if statement

how do I keep the word sector in it?

19. ## Re: V lookup and if statement

Something like:
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1