Excel excel - if field contains word post number in next column

Joined
Sep 13, 2012
Messages
2
Reaction score
0
Hi I am really stuck on this problem can anyone help?

I have a spreadsheet with a column (D) of words. If the word in cell D3 equals say 'Fuel' I want to post a number in column A3 = "7300"

I have worked out a formula to pick up the word fuel and post it as a number but i then need to have that formula running down the whole of my column A, and would need to amend each line to change the formula. Ideally i would like the formula out of the way on the page so that I can amend it as appropriate. eg

initally I want to search for 'fuel' and put in the number '7300' into column A (on the same line) BUT then i want to be able to change the formula to look for say 'Cash' and put the number '1230' in the column A on the same line as the word 'CASH'

7300 fuel
1230 cash
7504 personal
7300 fuel
7509 purchases

Is this possible????

Thanks

Tracie
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
this is my guess as to what you need. You would put it in column A at the top, then use the fill handle (bottom right corner of the cell) to click and drag to copy the formula down the column.

=IF(D3="fuel",7300,IF(D3="cash",1230,IF(D3="personal",7504,IF(D3="purchases",7509,"No Match"))))

That will work for the 4 different numbers in the example, and if you have Excel 2007 or newer, you can modify that for up to 64 different results (the limit of nested IF formulas). If you have an older version of Excel, you can only work with 7 that way, if you need more it's more work. Let me know if that does it! :D
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top