If and Mid function

[bill2016]

Hi All,

I'm just a newbie here so please forgive me if this question has been asked before - for this I didn't actually know where to start looking for help.

Just to start here is the layout of the sheet:

A | B | C | D | E | F |
1 | 14DC | Nguyen Van | Hiep | 180000 | |
2 | 02TK | Ha Van | Khoa | 50000 | |
3 | 10DK | Le Hanh | Nga | 120000 | |
4 | 01TK | Nguyen Van | Nam | 50000 | |

As a heads-up, column B=district, C&D are the surnames and first names respectively, E=wage, F=allowance.

The question is: Those who live in the district (column B) in which the third letter is "D" i.e in this case '14DC' & '10DK' are given an allowance (column F) of 300,000 and the others are zero. Is there a formula or function I can enter for column F that will detect the third letter 'D' in column B and assign 300,000 in column F in its matching row?

Thanks,
Bill.

[bill2016]

I tried to match the columns when writing the post but they don't line up when sending the post through. Just try to follow the letters with the respective columns.

Thanks,
Bill.

[Badger101]

Forgive the notation bu you should get the idea.

=IF(MID(RC[-4],3,1)="D",300000,0)

[nattasiray]

Forgive the notation bu you should get the idea.

=IF(MID(RC[-4],3,1)="D",300000,0)

Badger101 Write formula IN R1C1 Style, so I will convert IN ABC Style

At F1 use the below formula:

Please Login or Register  to view this content.

[bill2016]

Thanks for that!

Bill.