How can I lookup a value in Excel using two input cells ?

Hi Experts,

I'm trying to build an Excel formula which will use TWO lookup values to return a cell value in a range.

I've attached an example sheet of what I'm trying to do. It consists of a lookup table of 3 columns showing the distances in miles from a list of various UK towns to Birmingham and also to Manchester.

I want the user to select a town from the dropdown list in the 'From' cell, and to select a town from the dropdown list in the 'To' cell. On  a correct match in the lookup table, the value returned should be displayed in the 'Mileage' cell.

It is assumed that either Birmingham or Manchester will be selected from at least one of the dropdown lists.

I've tried INDEX/MATCH but can't get the result I need.

Many thanks
Toco
TocogroupAsked:
Who is Participating?
 
[ fanpages ]IT Services ConsultantCommented:
^ 423 (as expected) is the value for Aberdeen to Birmingham, & also for Birmingham to Aberdeen, in my suggestion.

PS. A slightly shorter formula; taking the lookup values of "Manchester" & "Birmingham" from the titles of the two columns (cells [F6] & [G6]):

=IF(OR(C5=F6,C5=G6),VLOOKUP(C6,E7:G17,1+MATCH(C5,F6:G6,0),FALSE),IF(OR(C6=F6,C6=G6),VLOOKUP(C5,E7:G17,1+MATCH(C6,F6:G6,0),FALSE),"Invalid"))
0
 
[ fanpages ]IT Services ConsultantCommented:
"I've attached an example sheet of what I'm trying to do..."

(Nothing attached)
0
 
CamyCommented:
Don't think you included the attachment?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
TocogroupAuthor Commented:
Sorry about that.
Mileage-Calculator.xlsx
0
 
Roy CoxGroup Finance ManagerCommented:
Just a very quick guess. The example uses two Forms ComboBoxes.
INDEX-FORMULA.xlsx
0
 
Roy CoxGroup Finance ManagerCommented:
Just seen the example, maybe this
Mileage-Calculator.xlsx
0
 
[ fanpages ]IT Services ConsultantCommented:
This formula may be placed in cell [C8]:

=IF(OR(C5="Birmingham",C5="Manchester"),VLOOKUP(C6,E7:G17,1+MATCH(C5,F6:G6,0),FALSE),IF(OR(C6="Birmingham",C6="Manchester"),VLOOKUP(C5,E7:G17,1+MATCH(C6,F6:G6,0),FALSE),"Invalid"))

Please see the attached workbook.
Q-28671036.xlsx
0
 
TocogroupAuthor Commented:
Hi Roy,

I seem to be getting the wrong mileage in some cases, For example, Aberdeen to Birmingham
0
 
TocogroupAuthor Commented:
That's great. Just what I wanted and works consistently.
Many thanks
Toco
0
 
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.