Search code examples
vbaexcelexcel-formulavlookup

Search column for string, return value from that row

I have a workbook with two tabs:

  1. REPORT_TAB and
  2. RAWDATA_TAB

My RAWDATA_TAB has standard database looking data:

   A          |      B      |      C      |    D
ACCOUNT_KEY   |    AMOUNT   |     DATE    |   FEES
 MGX4421           100         6/15/2018       26
 MGLR200           75          6/15/2018        5
 CXDSTR            150         6/15/2018       50
 18000             45          6/15/2018       10

On my REPORT_TAB, I want to search column A above for specific accounts, and return the value in column B.

For example:
For one cell I want to search for "CXDSTR" and return 150
For another cell, I want to specifically search for acct "MGX4421" and return 100

I thought I could accomplish that with something like:

=VLOOKUP("MGX4421", RAWDATA_TAB!A1:D10,2,0)

which would return 100, but it says #N/A with the green triangle in the cell.

Would this be possible with VLOOKUP, or easier with VBA? or MATCH/INDEX

Solution

  • I was able to find a solution for this. I couldn't get the VLOOKUP to do exactly what I was trying to do, so I used the INDEX-MATCH combo. Syntax looks like:

    =INDEX(RAWDATA_TAB!A1:T1000,MATCH("MGX4421",RAWDATA_TAB!C:C,0),2)

    This basically goes to the RAWDATA_TAB, searches for the row which contains the unique account number "MGX4421" and returns the column "2" which in the above example is the [AMOUNT].

    I hope this helps anyone