# Multiple Criteria Lookup Returns Name

Jan 12, 2008

I'm getting cross-eyed from trying to find the source of the error and not having any success. I've successfully used this approach many times, but for some reason I'm getting an error I can't pin down.

The problem: INDEX-MATCH lookup formula returns # NAME error. Formula: =INDEX(Data,MATCH(H8,Date,0),MATCH(\$I\$7,Line,0),MATCH("FPY",Hdngs,0)). I've done my best to verify the named ranges and cell references are correct and have stepped through the help file on the "potential source of the error", including going to Tools >Options and making sure "Accept Labels in Formulas" is checked, as well as having read a number of other posts.

## Lookup With Multiple Possible Returns

Mar 31, 2009

I need to lookup a Order Number and return all associated Item Numbers. My spreadsheet has about 10,000 lines and these Order Numbers could have as little as 1 or as many as 15 Item Numbers associated. I would like to have a drop down on each Order Number that shows the Item Numbers.

## Lookup That Returns Multiple Values

Jun 9, 2009

I'm trying to do the next level of a v-lookup. I have a group of data with a text "flag" on certain rows. On my summary worksheet, I'm trying to do a lookup or a nested index function retrun all of the values with the text flag next to it.

I've been playing around with nested formulas and this is where I'm stuck. Here's what I came up with:

=INDEX(\$D\$1:\$E\$9,(SMALL(IF(\$D\$1:\$D\$9=\$A\$1,ROW(\$D\$1:\$D\$9),ROW(1:1)),ROW(1:1))),2)

This only works for one row and not for the other rows. Maybe I'm going down the wrong path?

## Lookup Multiple Criteria & Sum Result On Multiple Criteria

Oct 21, 2006

I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)

This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.

So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30

The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.

## Excel 2010 :: Lookup Multiple Criteria Across Multiple Sheets?

May 28, 2014

I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..

What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)

## Multiple Criteria Lookup ..?

Mar 9, 2009

I'm trying to figure a formula that provides a numerical value when certain criteria are met across a range of cells. The criteria are numerous though and here is where I'm having trouble.

For example, the formula needs to look at Cell A5, then at a range of cells (C5,G5, E5, I5) and depending on the value of those cells provide a predetermine result in Cell K5.

For example, IF (A5 = 'A', and C5=1 or G5=1 or E5=1 or I5 = 1, then P5 must = 15), or if (A5 = 'A', and C5 or G5 or E5 or I5 = 2, then P5 must = 10), or if (A5 = A, and either C5 or G5 or E5 or I5 = 3, then P5 must = 6), or if (A5 = A, and either C5 or G5 or E5 or I5 = 4 then P5 must = 2), or if (A5 = A, and C5 or G5 or E5 or I5 = 5, then P5 must = 1)…….this needs to be duplicated for a list of possiblities.

## Using LOOKUP With Multiple Criteria

Dec 26, 2012

I'm making a schedule grid has varying start & stop times. I've made a table so the people using it can check that there is overlap when they are scheduling themselves and the members of their shift.

The rows have the employees and the columns has the hours, (one row for each hour in the year 2013)

Each employee has a 1 under each hour where they work and they can easily see where their shift overlaps onto the next persons and by how many hours. I also have a row on the bottom that checks how many people are on at any given time so everyone can check that we have a minimum number of people on and a maximum number as well.

As it is pretty difficult to look at this grid as it is, i made a 2nd table which we could print to see which days we shoud come in and what time. (with each column being the day/ date and the cell having the time frame one comes in.)

The problem i have is that if any adjustments are done to the first table, (with the hourly columns) it isn't reflected in the 2nd table.

I've created a LOOKUP table with each hour/ shift, but i do not know how to have lookup check the starting hour, the ending hour, and what date it is.

I was considering placing a look-up formula in each cel for each employee for each day for the entire year, i just don't know what the formula should look like.

## Lookup With Multiple Criteria

Dec 27, 2012

I have a spread sheet with multiple columns. I need a formula to look at criteria from three specific columns in the spread sheet and spit out a description from a key I created. Here is an example.

This is the key I created:
Record Kind
Group
Group Name
Category

[Code] .....

The spreadsheet has the same columns so I need a formule to look in the spreadsheet and if it sees each combination as in the key to spit out the category name from the key.

## Lookup Table Using Multiple Criteria

May 17, 2013

I have attached a demo spreadsheet to define the problem.

I wish to populate column K (Risk Rating) with data retrieved from the table, based on the corresponding information from Columns I & J.

e.g. I5 + J5 = Short term illness or injury + Unlikely. This corresponds to cell E9 (11) in the table so required response in K5 would be 11.

Help Book1.xlsx

## Lookup Multiple Criteria In A Cell

Jun 3, 2014

I have a cell that contains a long string of text.

I want to be able to lookup in it to see if any word from a list is in it, if it is to return text dependant on which word is in that cell.

Seems like it should be easy but looking up the multiple values is making it difficult.

## Multiple Criteria Lookup Functions?

Aug 6, 2014

I've attached a sample of the data I'm using.

I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).

Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.

Spreadsheet 2 is the output I need and the format is required by other stakeholders.

In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.

Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.

What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.

## Static Multiple Criteria Lookup

Sep 19, 2009

i need to track tyres, i'll have to use the Serial Nos. of the tyres inscribed on them. What i require is to lookup the serial no. from Purchase/issue sheet to my Maintenance sheet. To do that there'll be 3 criterion to match to get the correct Serial No.

In my Purchase sheet suppose if a purchase a tyre and issue it to some vehicle on 11th September, i'll have its first inspection for maintenance after mebbe a month (no fixed intervals). So if i check on 10th October the lookup should actually find the greatest date lesser than the inspection date and keep it to that. I know that the lookup function would get the last unit in case of duplicate entries but that becomes dynamic and the serial no. would change once theres another entry matching the 3 criteria. And dat would mess up my previous record.

## Lookup With Multiple Criteria...sumproduct

Jan 26, 2010

Attached is my sample workbook. There would normally be 600+ employees with multiple rows per employee. I would like Cell O3 in the Premium Calculation Worksheet to look at the Premium Contribution Report, and if Row A contains the employee number (A3) AND row C contains "H&D" I would like it to sum row E.

I included the sumproduct formula I tried to put together but I'm getting an error, so I'm not sure what I've done wrong. The reason I have it referencing "O2" instead of just inputting "H&D" is that O2 could be any number of plans - I have multiple rows with different plans and I need it to pull in all the data.

## Use IF/Lookup Function In Multiple Criteria

Apr 30, 2009

I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)

I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )

excelformula.gif

pricing.xls

## Lookup Function On Multiple Criteria

Mar 21, 2012

I have two documents, and I need to search data in document 1 based on information given in document 2. On document 1 I have ID number and date range and on document 2 I have ID number and date. Date needs to be in the date range, and ID numbers must be the same.

Please see tables below for the reference.

Document 1
Document 2 ID Start date End Date Data
ID Date Data 11 01/01/2009 31/12/2010 Data 1
11 05/05/2011 22 01/01/2010 31/12/2010 Data 2
22 08/08/2010 22 01/01/2011 31/12/2011 Data 3
33 01/01/2012 33 01/01/2012 31/12/2012 Data 4
11 02/02/2009 33 01/01/2010 31/12/2011 Data 3

11 01/02/2011 31/12/2011 Data 2

## LOOKUP ONE CRITERIA IN MULTIPLE COLUMNS

Oct 25, 2007

I am trying to run a lookup on a rather large table.

Column A Column B Column C Column D Column E Column F

Postal code City Province Postal code City Province

I am trying to look up the City and Province based on the Postal code and can't figure out how to do this.

There are too many Postal codes to fit them all in Column A, I have tried V Lookup, Index Match and can't get it to work.

## Lookup Values- With Multiple Criteria

Nov 15, 2006

I want to lookup values from one rawdata worksheet and have values appear on another sheet, but using 3 lookup values as the criteria. I cannot do this function with Vlookup as it only takes one criteria. I have looked for similar threads in this forum and came across an Index/Match function, but I cannot get it to work. I am attaching file. The data is in the 'rawdata' worksheet. I want values to appear in 'Input' worksheet, grey area using the 3 criterias (Dept number, GL number, and Seg code number).

## Multiple Criteria Lookup Between Workbooks

Aug 10, 2007

My workbook is tracking how many products have been sold and to whom.

Customer Market Apples Oranges etc (10 products) POSTING
xyz South 100
xxx South East 600

I have a second workbook that works out how much it costs us to send the goods and this is a matrix based on where our customer is and which produce they have purchased.

South Apples Oranges Pear etc
South East \$x \$y \$z
North etc
North West
etc

I would like to write a formula under the POST column that takes into account:
The Market. The Produce type (the column heading) based which cell contains a value
i.e using above Row 3 criteria would be Market = South and Produce = Apples. These two pieces of information are then to be used to go to the workbook containing our postage costs and picks up the relevant cost for Apples in the South region.

## Multiple Criteria Lookup For Smallest Value

Aug 23, 2007

I have a workbook with 4 sheets with Carrier rates based upon weight breaks and cities. I.E. Carrier 1 services City 1 @ 1.30 per LB for 0 -100LBS and 1.55 for 101 - 199LBS. What I need is a forumla so that someone can input the city and weight on sheet 1 and it will search the 4 sheets and return the cheapest Carrier in the event that there is two carriers with the same rate it would return both. It would also need to return the rate for that city and weight.

For example

User inputs "City 1" "112 LBS" formula searches the 4 carrier sheets and returns values "Carrier 1" & "1.55" as this is the cheapest carrier and the rate they charge per LB for this city.

## Multiple Criteria Lookup Text Result?

Dec 10, 2013

So basically I am looking to build a formula that would take this information and return a text value.

if it could work anything like this sumproduct formula to bring back text that would be awesome:
=SUMPRODUCT(--(A1:A10="7654321"),--(B1:B10="ABCDE"),--(C1:C10="3"),D1:D10)

But instead of a formula that returns a sum of values, the return is a single value. The three criteria will always be unique. There will never be a case where there is a duplicate of all three.

## Lookup/Search Multiple Columns/Criteria

Apr 9, 2008

I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.

## Multiple Criteria Lookup With Date Range Match?

Dec 30, 2008

I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.

## Multiple Criteria Lookup With Date Range Match

Oct 12, 2012

I'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.

I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.

## Lookup Multiple Criteria In Different Tabs To Return Result

Jun 5, 2008

I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.

File is attached.

## Multiple Criteria Lookup With Date Range Match

Dec 30, 2008

I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.

## Formula To Lookup And Sum Cells Based On Multiple Criteria In Row And Columns?

Jun 17, 2014

I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.

Test2014.xlsx

## Lookup With Mutiple Returns

Dec 16, 2008

i am trying to lookup up when ppart matches spart and subtract total shipped from total produced. Here is where I am getting stuck .... because spart has multiple returns LOOKUP(2,1/((I2=C:C)),J2)

## LOOKUP Returns Wrong Or Odd Value

Mar 27, 2007

I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?

## Lookup Function Returns #N/A Or #REF

Apr 23, 2007

Im trying to do a lookup but when I drag the formula down it returns the wrong values and also returns #N/A or #REF (which I think means it's refering to a wrong range and cant find the data???). How can I do a look up function by dragging the formula down a long column of data eg. 65,000 rows in excel, that returns the correct text. I have attached a simple example- see attached.

## Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.