CODEWITHSUNDEEP
excelexcel-formulamatchexcel-2007lookup
newkid59
newkid59

Reputation: 19

Excel Lookups or Index Matches with Multiple Criteria

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.

Upvotes: 0

Views: 553

Answers (1)

MutjayLee
MutjayLee

Reputation: 568

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)),"")

Upvotes: 1

Related Questions