Driving Miles

I am trying to create a spreadsheet that depending on the Store # I have to drive to, it will insert the number of miles I travel round trip. I drive to approx 50 different stores in my area for work and would like to enter into cell (for example) B4 the store number (example 501) and then in cell C4 it will automatically enter the number of miles that drive is.

I am using this formula right now but need to add to it and dont know how to connect multiple commands.

=IF(OR(B4={502,503,504,505}),"30","")

in that command The Store Numbers 502,503,504 and 505 are 30 miles away but I need to add stores (for example) 506,507,508 that are 40 miles away.

Any help is appreciated.

I would suggest that you set up a 2-column table somewhere, e.g. in cells X1 to Y50, which has the store numbers in column X and the distances in column Y. Then in C4 you can use the formula:

=IFERROR(VLOOKUP(B4,X$1:Y$50,2,0),"")

You might need to add *2 to the VLOOKUP part, if your distances are for a one-way trip.

Hope this helps.

Pete

Pete,

If I do that can I copy and paste in in cells below without it effecting the formula?

Thanks

Yes, the B4 will change to B5, B6 etc. when you copy it down, so that you can put other store numbers in those cells, but the table X$1:Y$50 will remain the same in the formula.

Hope this helps.

Pete

Pete,

I am getting a #name? error. I changed the x$1:y$50 to the appropriate cells that are represented in my spreadsheet as I had already created that two columns you suggested.

Any ideas why I would get that error?

Thanks

That error usually means that Excel doesn't recognise a function name or named range that you are trying to use in a formula. There are only two functions in the formula that I gave you - IFERROR and VLOOKUP - have you spelt those correctly. What version of Excel are you using?

Post the formula that you tried to enter.

Pete

i copied and pasted the formula you posted

=IFERROR(VLOOKUP(B4,M$4:N$50,2,0),"")

Wow..that did it!

Thank you so much!