Excel Lookups or Index Matches with Multiple Criteria

Question

I need some simple Excel help. I'm fairly new to formulas in Excel, and while I know my request is simple, I just can't get the answer I want on my own. So in my Excel workbook, our data has the following categories: Acct. Number, Region, Contact (etc.), and the acct number data is broken down into separate lines depending on what region it is in. So Acct. Number 121 is broken down into 3 lines (North, South, West), and different data pertains to each region. What I'm trying to do is to create one line in another sheet for each account number. I've been able to do this with a VLOOKUP for all of the numbered data, but I've run into a problem with non-numerical data.

So, example. I am trying to find the contact of the "North" region, and put it into the cell of my new worksheet. I have the account number listed on each line, but the regions are listed as the Column names

This is how the data is listed in 3 columns from the source we are given:

   Contact....   Acct No....   Region.......
    Joe...........121.............North
    Marcy.........121.............East
    Jane..........122.............South
    Bob...........122.............West
    Bill..........122.............North

And this is the set up in my separate worksheet that I've created:
Acct.No.........North Revenue.........North Contact......South Revenue....South Contact.....
121.................803.........................(Need this value).....
122.............. ..122..........................(Need this value).....98.......................(Need this value)

I've done perfectly fine getting the numerical revenue values through VLOOKUP, referencing the Acct. No and specifying & North (or other regions), but when I try to do that same method to get the contact names, I get a #N/A. I've tried Index and Match combos that I've found online, but then I end up getting #Value errors.

So is there a way to get the contact name from the data sheet, when I want to look up by the Acct No. in the cell of my worksheet, and then specifying the region on my own since I don't have a specific "North" cell to reference in my lookups or matches?

Thank you for all of your help, I know this has a simple answer, I just haven't found my way around it yet.

What was the formula that worked before running in to non-numerical data? — MutjayLee
So this is the VBA code that I got the numerical values for the North Revenue: "=VLOOKUP(RC[-13]&""NORTH"",Detail!C[-11]:C[8],20,FALSE)" But if I switched the columns to give the contact, I get a #N/A result — newkid59
@newkid59 - So you're trying to use that Vlookup() to return the value in column A, based on a lookup of the Account number? You mentioned Index/Match in your title, do you know how to use Index/Match? If so, you can easily look up based on two+ values, see this page to get you started. Does that help, or am I misunderstanding the issue? — BruceWayne
You're right, I'm trying to return Column A based on the Acct. Number in Column B, but also corresponding to the specific region from Column C. So I did the Index and Match from the page, but I'm returning a #Value! error. So this is the formula that I've used for Index and Match now (and this was just in the worksheet, didn't do VBA yet): =INDEX(Detail!A:A,MATCH(A8&"North",Detail!B:B&Detail!C:C,0)) Am I supposed to do something different since it's nonnumeric? — newkid59

MutjayLee MutjayLee · Accepted Answer · 2016-06-07T02:36:51

Sample data, I had the following

Column A    Column B    Column C    Column G    Column H    Column I

Contact     Acct No.    Region      Acct No.    North Rev   N. Contact
----------------------------------------------------------------------    
Joe         121         North       121         803         *Formula*

Put below formula in Column I (in my case)

=IFERROR(INDEX(A:A,MATCH(G2 & "NORTH",B:B & UPPER(C:C),0)),"")

Make sure you end it with Ctrl + Shift + Enter (This is array formula)

Copy and paste down

Note : I used UPPER to check for any north word (North, NoRtH, NORTH....etc)

In addition, if you have Phone number of the contact in...let's say column D

Then you can change formula to

=IFERROR(INDEX(A:A & ": " & D:D,MATCH(G2 & "NORTH",B:B & UPPER(C:C),0)),"")

Excel Lookups or Index Matches with Multiple Criteria

1 Answers