Lookup a value in an excel table

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
Who is Participating?
BusyMamaConnect With a Mentor Commented:
You need an array formula.  This one combining INDEX and MATCH works.


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.
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.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Here is the answer attached.
I work with similar cases .

Rob HensonFinance AnalystCommented:
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.

Rob H

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.

vmccuneAuthor Commented:
How do I hide the #NA if it does not find a value?
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")


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

Hope it helps.

vmccuneAuthor Commented:
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.
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.