Excel Vlookup Incorporating SQL Table

Excel vlookup incorporating SQL table

Good news is you can do this without VBA, quite a few steps though as follows:

1 . First add a new sheet, call this something meaningful like PoscodeLookup.

2 . Next go to Data and select Other Sources and Microsoft Query:

3 . Next select (or create) an ODBC data source that can connect you to your database asd select this (you may need to enter user/pass).

4 . The query designer will ask you to select a table, just click close.

5 . Next select the SQL button:

6 . Now enter the SQL query to get the single value you need, using an example postcode eg:

SELECT TOP 1 [Value] FROM [MyTable] WHERE [Postcode] = 'AB12 1AA';

7 . Now hit OK and OK which should return a single value in the window.

8 . Click on Return data in the toolbar:

9 . Now back in Excel hit properties in the prompt:

10 . Now change the post code you entered into a ? in the definition tab:

11 . Hit OK and OK again and it will prompt for a value, enter a valid postcode and hit enter, this should now give you the value you want in cell A1.

12 . Now click in the cell A1 and go to Data > Properties then Connection properties:

13 . Now in the definition tab you have a Parameters button at the bottom, in here you can fill out as below:

Note, the target cell is the cell where you enter the postcode, tick the refresh box if you want this to re-run the query when the post code changes.

That should be it, let me know if it doesn't work.

VLOOKUP and Transform a table inside the formula

As per your explaination, it looks like you need to use wildcards. Within VLOOKUP:

=VLOOKUP("*"&RIGHT(B2,3),A:A,1,FALSE)

But I would suggest get in the habit of using INDEX and MATCH combo, since it's faster:

=INDEX(A:A,MATCH("*"&RIGHT(B2,3),A:A,0))

VBA vlookup on table produced by Connection String

Issue is that the code is continuing while the query is still refreshing, so the vlookup has been performed before there is data to search. Fix is to disable background refresh: Data -> Connectons -> Properties -> untick 'enable background refresh'

SQL query to replicate INDEX/MATCH functionality of excel

A simple JOIN should be what you need, using both fields Parent_SKU and Color. You want to use a LEFT JOIN in case there is no matching record in Product_Mapping.
Conceptually, this achieves the same functionnality as Excel VLOOKUP.

SELECT
    pc.Parent_SKU
    pc.Color
    pc.UID
    pc.Date
    pm.Child_SKU
FROM
    Product_Catalog pc
    LEFT JOIN Product_Mapping pm
        ON  pm.Parent_SKU = pc.Parent_SKU
        AND pm.Color      = pc.Color

2-Way Table Look Up in Excel Using VLOOKUP and MATCH Returning Error

Credit to @barryhoudini and @DaveSexton for the solution... I'm just documenting it officially.

First off, the error I was receiving was due to the formatting of the source cell. So, per Barry's suggestion, I concatenated a "" to the end of my cell reference, which formatted the source cell as text, allowing the function to work. New code:

=VLOOKUP(A97,A4:L10,MATCH(B96&"",A4:L4,0),FALSE)  

Second, per Dave's suggestion, I abandoned the VLOOKUP/MATCH approach in favor of the INDEX/MATCH/MATCH approach. E.G:

=INDEX(A1:E14, MATCH(H2,A1:A14,0), MATCH(H3,A1:E1,0))

Thanks both for your help.

Calculating a table of units with cost defined via vlookup

You can combine a lookup with SUMPRODUCT:

=SUMPRODUCT((LOOKUP($B$1:$E$1,$A$7:$A$10,$B$7:$B$10))*B2:E2)

---

If the cost were in row 2 a simple SUMPRODUCT would suffice:

=SUMPRODUCT($B$2:$E$2,B3:E3)

Excel VLookUp But Only Once

Here's an array-formula requiring the following structure:

Make & Model are combined into a single cell for both customer requests and your stock.

Your stock has named ranges for its 2 columns: MakeModel and Serial. The ranges can include headings, but they MUST both start in row 1.

Your customer requests start in A2.

Enter this array-formula in B2 only:

=IFERROR(INDEX(Serial,SMALL(IF(MakeModel=A2,ROW(MakeModel)),COUNTIF($A$2:A2,A2))),"Insufficient Stock")

To enter an array-formula, you need to press CTRL+SHIFT+ENTER. After entering the array formula, then fill it down.

Excel UDF for VLOOKUP(MATCH()) that uses structured table references

I'm not familiar with that structured reference syntax, but have you tried constructing the formula and using the worksheet Evaluate() method?

Here's a simple example:

Function TestEval(s1 As String, s2 As String)
    TestEval = Application.Caller.Parent.Evaluate(s1 & "/" & s2)
End Function

EDIT: After checking out the whole structured table thing, this seems to work:

Function TableLookup(val, tbl As Range, colName As String)
    Dim indx, rv
    indx = Application.Match(colName, tbl.Rows(1).Offset(-1, 0), 0)

    If Not IsError(indx) Then
        rv = Application.VLookup(val, tbl, indx, False)
        TableLookup = IIf(IsError(rv), "Not found", rv)
    Else
        TableLookup = "Col??"
    End If
End Function

---

Related Topics