[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# Look up values in table

Posted on 2013-01-21
Medium Priority
239 Views
Hello Experts!

I'm having trouble getting the correct value in Quotation!B15. The formula should reference Freight!AE1:AI1000 , Airport (Quotation!B10) and Weight (Quotation!B13)

lookup-Ship-As-rate.xlsx
0
Question by:tomfolinsbee

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 38803991

=INDEX(Freight!\$AE\$2:\$AI\$1001,MATCH(Quotation!\$B\$13,Freight!\$AE:\$AE,0),MATCH(Quotation!\$B\$10,Freight!\$AG\$2:\$AI\$2,0))

Note how you make the reference table start in row 2, but in the first MATCH expression you start in Row 1 because you use the entire column.

For starters:

=INDEX(Freight!\$AE\$2:\$AI\$1001,MATCH(Quotation!\$B\$13,Freight!\$AE\$2:\$AE\$1001,0),MATCH(Quotation!\$B\$10,Freight!\$AG\$2:\$AI\$2,0))
0

LVL 43

Expert Comment

ID: 38804001
=INDEX(Freight!\$D\$3:\$I\$6,MATCH(Quotation!B10,Freight!Airport,0),MATCH(Quotation!B13,Freight!D2:I2))
0

Author Closing Comment

ID: 38818724
Thanks for this. A bit of trial and error and it worked. Cheers.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month9 days, 1 hour left to enroll