Solved

Lookup a value in an excel table

Posted on 2011-09-07
10
234 Views
Last Modified: 2012-05-12
I am trying to work with an excel table that I pull into excel from Access.  

On the exclosed worksheet I want to type in the two values in yellow on the Offer tab and lookup the corresponding numbers in the data tab which contains the table.  (the number of rows may change in the table each time I import).  I do not know what is the most efficient way to do this and be able to replicate the lookup down an entire column.  

I asked this previously and got great help but I now figured out enough that I think a table in excel may be faster.  Any guidance on all of this is appreciated.  TableLookup.xlsx
0
Comment
Question by:vmccune
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 4

Expert Comment

by:SafetyFish
ID: 36495780
My initial thought is that you're probably going to use a vlookup (vertical lookup) function. I'll have a look at the workbook and see if I can offer any more information.
0
 
LVL 7

Accepted Solution

by:
BusyMama earned 500 total points
ID: 36495958
You need an array formula.  This one combining INDEX and MATCH works.

=INDEX(Data!F:F,MATCH($B$2&$B$5,Data!A:A&Data!D:D,0))

You have to make sure to enter the formula using CTRL+SHIFT+ENTER in lieu of just hitting ENTER.

This returns column F (the first argument).  Change just the F:F to G:G for the unitprice, and so on.
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36496008
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 6

Expert Comment

by:akajohn
ID: 36496055
Here is the answer attached.
I work with similar cases .


TableLookup-aka.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 36496064
As suggested in the previous question I believe a SUMIFS would do what you require.

I can't put this into your actual file because I am working on a machine with xl03 at the minute but with the following assumptions the syntax would be:

In column C UNITCOST =SUMIFS(Data!$A:$A,$B$2,Data!$D:$D,$B5,Data!F:F)

Copy across to the right and the F:F will change to G, H, I & J but the other references will stay the same.

As mentioned previously, the only assumption is that there is only one entry that meets the Offer & Style criteria.

Thanks
Rob H

0
 
LVL 6

Expert Comment

by:akajohn
ID: 36496099
I have added a column called Join. The advantage of this solution is that it uses the connection to access. When you created a connection Excel created a named range corresponding to this table data. (This get updated and shrinks or expands ,each time you refresh you connection). You do need to recreate the connection everytime. Excel Remembers.
The "Join2 Column will be automatically filled by excel and is used fro the efficient lookup.

On another note ,this is much easier to do in SQL if you have full access to the Access Database!

Any questions, please ask.

A>
0
 

Author Comment

by:vmccune
ID: 36496561
How do I hide the #NA if it does not find a value?
0
 
LVL 6

Expert Comment

by:akajohn
ID: 36496724
you wrap the formula using iferror function, assuming you are using Excel 2007 or later.


E.g to display blank

IFERROR(formula_here,"") or to display not found  =IFERROR(formula_here,"NOT FOUND")

e.g

=IFERROR(INDEX(Table_TOGBuyerReview.accdb[UNITCOST],MATCH($B$2&B5,Table_TOGBuyerReview.accdb[Join],0)),"NOT FOUND")

Hope it helps.

A>
0
 

Author Closing Comment

by:vmccune
ID: 36497274
Perfect!
0
 
LVL 4

Expert Comment

by:SafetyFish
ID: 36497884
A little late, but here is an alternate solution. This workbook dynamically grabs a distinct list of values from the selection columns and then returns the required values based on the choices in the drop down boxes.
TableLookup.xlsx
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Consolidation 8 19
locking multiple column ranges 10 22
VBA Works in Excel 2010 Not 2016 Help! 5 17
And OR formula 5 20
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now