Excel math question

[paperfist]

Ok not exactly a math question, but I need to compute an answer based on what 2 cells contain.

So this is for a mileage calculation for which employees may travel to a combination of buildings North, South, East and West.

On my Excel sheet I have a drop down box that lets me pick any of those 4 locations, North, South, East, and West. I have another set of cells that has the mileage calculated between all combinations of those buildings

If I select in say Cell B2 North and B3 East I would know that was 4.2 miles, but how can I get Excel to 'read' what is in Cell B2 and B3 and pass on the mileage number into another cell?

Thanks!

 

[Tweak155]

Would an IF statement work?

Example:

=IF(B1="east",0,10)

Can get real messy with a lot of nested IF's, but if this is all you need it for, may be fine. Otherwise, there are definitely more elegant solutions.

 

[IronWing]

Take a look at the VLOOKUP function. It allows you to pull in values from a referenced table. Naming the reference table helps with building VLOOKUP formulas so you may want to read the Excel help info on using names as well.

 

[Phil L]

VLOOKUP is indeed the best option here, provided your table is formatted in a convenient way for the look-up function. Since it sounded like you need the result of both B2 & B3 cells for the look-up, you can concatenate them as your search key. E.g. your look-up table could be like this

.............. (column E) ........ (column F)
(row 2) .. North-North ..... 0
(row 3) .. North-East ....... 4.2
(row 4) .. North-South ..... 3.4
(row 5) .. North-West ...... 2.0

Then you can do
=VLOOKUP(B2&"-"&B3,E2:F5,2)

- whereas B2&"-"&B3 concatenates the 2 cells you talked about, and return "North-East" as the look-up key. You can of course format it to different way that suits your need, this is just an example
- the key is used to search the first column of the table that span cells E2:F5
- finally the function returns the corresponding data in the same row as the search key and in the 2nd column of the E2:F5 table (so in this case that's column F)

 

[paperfist]

Awesome thanks a lot guys!

I'm not at home to try it, but will work with your ideas later.

I'm trying to work out in my head what was posed and I think I may have left something out.

I need to be able to read what's in cell A1 and B2 for example to compare them in order to look up the mileage value from another cell and then return that value.