Multi Test Lookup

Dec 8, 2007

I have a rather complicated question, complicated for me. I have one workbook with 24 worksheets. The first three worksheets are names of people, ie kim smith, jack white, bill blue. The next 23 sheets are held in reserve for new people and are currently labeled isr 4-23.

I have used your site to get to a point where I can generate a cell to reflect the tab name. I have used these formulas to accomplish that:

=CELL("filename",'ISR 4'!$A$1)

="'"&MID(F6, FIND("]",F6)+1,256)&"'!"

This generates 'ISR 4'! in the destination cell. I have taken this list of sheet names and crated a dropdown list. Now I would like to apply two tests to this list as well as another list.

SheetnamesWeekofYearHits

Depending on who is picked in the sheetname list and depending on what week is picked in the week list I want to go to the sheetname tab, that week and grab cell b1 or b2 (week 1 b1, week 2 b2 and so on for 52 weeks)

View 9 Replies


ADVERTISEMENT

Multi Condition Date Cell Test For Month & Year

May 18, 2009

Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.

View 3 Replies View Related

Sum As Per Multi-lookup

Jul 31, 2008

In the attached file I managed to sum up the Quantities per Material & Category. The Original DATA is presented in columns A:E. The requested results/Formulas are at Cells B2:B4. My Question is very simple: Is there a way to sum up the Quantities WITHOUT the Help-Column G !? If so - how ?

View 9 Replies View Related

Multi Lookup A Name And Its Corresponding Code

Jun 16, 2008

I am currently using the formula below to lookup a name and its corresponding code. I type the code into cell A13. The name is fixed at B1. This formula then finds the name (B1) in a table and matches the column header of the code which I type into A13 and returns a value. This allows me to type in different codes and quickly see the value corresponding to the name. I also want to be able to type the coding into A13 and be able to see a listing of all of the names attached to that coding.

VLOOKUP(BETA!B1,MAINTABLE,MATCH(BETA!$A13,MAIN!1:1,0),FALSE)

View 9 Replies View Related

Multi-level Lookup

Oct 24, 2007

I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...

=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)

The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......

View 3 Replies View Related

Multi Criteria Lookup

Dec 19, 2007

I setup a Vlookup formula (in sheet 1) to find the figure in column C (in sheet 2) based on the company name and branch name but the result is incorrect.

View 3 Replies View Related

Multi-Variable Lookup Table

Jun 12, 2014

I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.

Screen shot 2014-06-12 at 12.36.47 PM.png

View 1 Replies View Related

Multi-Range Lookup With Export

Apr 27, 2012

I'm trying to create a formula that looks for matches in 3 ranges and exports a specific value dependent on if there is a match or not into a new cell.

IE:

If any value in Column A = D1 AND any value in Column B = E2 then enter corresponding value from Column C into F2, if not then place "New Data" in F2.

If it makes it easier to conceptualize: Columns A,B,C are in one worksheet. Columns D,E,F are in another worksheet.

Worksheet 1
Column A Column B Column C
1 Frog Eye Head
2 Rabbit Eye Head
3 Cat Nose Head
4 Horse FR Hoof Leg
5 Dog Nose Head

Worksheet 2
Column A Column B Column C
1 Horse Eye "New Data" (no match from column A or B)
2 Cat Nose Head
3 Dog Nose Head
4 Rabbbit FR Paw "New Data"
5 Horse FR Hoof Leg

I need a Formula for Worksheet 2, Column C that searches Worksheet 1 Columns A & B and places the corresponding match from Column C (if there is a match).

I've tried Lookup and IF/AND formulas to no avail.

View 9 Replies View Related

Multi Criteria With MATCH Or LOOKUP Formula?

Mar 20, 2014

What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..

and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.

and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..

View 3 Replies View Related

Formula To Lookup Multi-criteria Information

Dec 20, 2013

I am using a list like this.

A
B
C
D

1
Teacher
Name
Color
Size


[Code] ....

I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.

Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.

Teacher
Red
Green
Blue
Size 3

[Code] ...........

View 5 Replies View Related

Multi Value Lookup With Tolerance For Rounding Differences

Sep 4, 2008

a lookup and match of two values.

For example, in Sheet1 I have a unique transaction reference number and a £ value.

eg. 60231568 £38.05

In the next sheet (Sheet 2), I have a summary of data, and I need to return the company name...

60231568 £38.04 Company X
60231568 £15.12 Company Y
60231568 £10.11 Company Z
60245871 £105.11 Company Q

The look up needs to match trans no, amount (within 3 pence threshold) and return in this example company X.

View 9 Replies View Related

Multi Condition Lookup Formula/Function

Dec 11, 2007

I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.

View 5 Replies View Related

Return Unique ID Based On Multi-Column Lookup

Dec 18, 2008

I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system.

I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip

Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST:

SCH0001, Lindsey Schubert, 75230
SCH0002, Thomas Schoembs, 53132
ADA0001, Samantha Adams, 28205
...

What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary.

Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.

View 3 Replies View Related

Return Cell Reference From Multi-Column Lookup

Dec 13, 2006

i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)

it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!

i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).

any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!

should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?

View 8 Replies View Related

Lookup Values From A Multi Dimensional Table / Data Sheet

Oct 24, 2007

I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex)
On the report I have values 123A and 1234A
On the data tab I have table that has.

Ref Tot Value Desc Value1 Value2 Value3
1 123A Widgets 1 2 3
2 123A Widgets 4 5 6
Ref Tot Value Desc Value1 Value2 Value3
1 123B Nuts 7 8 9
5 123B Nuts 1 3 5
Ref Tot Value Desc Value1 Value2 Value3
7 1234A Bolts 2 4 6
11 1234A Bolts 3 5 8

So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.

View 14 Replies View Related

Test Two Cells And Set A Third Cell With Different Values Depending On Test Results

Aug 21, 2013

I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.

A1 represent a number of years and B1 represent gains or losses (negative)in dolars.

The way I see the logic is as follows:

If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)

If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.

On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.

I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:

If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL

View 3 Replies View Related

Multi Condition Format? Lookup And Date Condition Valid For

Aug 13, 2009

I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.

View 6 Replies View Related

Return Row Index Of Selected Items In Multi Column Multi Select Listbox

Jun 30, 2014

I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:

[Code] .....

But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.

View 5 Replies View Related

1 Column Into Multi-columns And Multi-rows

Jul 16, 2013

Let's say I have one column of;

1
2
3
4
5
6
7
8
9

What is the most efficient way to change this into '3-columns & multi-rows' like this?:

1 2 3
4 5 6
7 8 9

The actual list is a lot longer and numbers are not in order.

View 7 Replies View Related

Concatenate Multi-row Cells Into One Multi-row Cell

Aug 21, 2013

I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:

Desired Result

First
First
Name
Name
First Name
First Name

View 2 Replies View Related

Test For Next Available Row

Feb 10, 2007

I am trying to test for the next available row in a sheet using a range as follows:

NextRow = Range(MyRange).End(xlDown).Row + 1

If there are no rows being populated, the formula throws an error and I assign NextRow to 0 indicating no entries present

However, and this is my question, if there is one row populated the value for next row seems to either give 0 or 65637.

After this, the code runs OK.

View 9 Replies View Related

Test If A Cell Has #N/A

Nov 9, 2009

How do I test if a cell has #N/A?

View 4 Replies View Related

How To Test A Formula To See If Particular Value Is Used Or Not

Jun 27, 2013

From a large worksheet I'm trying to find out if a particular value is used in that formula. E.g. formula: (In cell C1)=A1+B1*.75

I want to check if .75 is used in the formula (which is 75%) There are 1000's of record would it be possible to check the entire column C

View 6 Replies View Related

How To Test If Value Is Integer

Apr 9, 2004

I am using the IsDate() and IsNumeric functions in my VBA code. Is there an equivalent function for testing if a value is an integer?

View 5 Replies View Related

Formula To Test A Value

May 11, 2007

I'm having an intelligence lapse and I need help with a formula

Simply need to return a "1" if value in Cell X is equal to or greater than -(minus)4% and a "0" if value is less than -(minus)4%.

View 9 Replies View Related

Timing A Test

Jul 16, 2008

I have a 32 question test that I expect everyone to get most (if not all the answers correct. I have a cell that is counting the number of correct answers.

What I want to do is start a timer when the first correct answer is put in and stop when the last answer is put in. So essentially, I need a way of looking at a cell and starting a timer when it equals one and stopping when it hits 32.

View 9 Replies View Related

Test To See If The Autofilter

Mar 19, 2009

How i can test to see if the autofilter, while enabled, is not currently filtering?

View 9 Replies View Related

Cochran's Test

Jan 23, 2007

how to perform Cochran's Test in Excel?

I know it's probably available somewhere as an add-in, but we aren't allowed to install any, so we have to do it from scratch.

Basically, I'm have to set up a spreadsheet to look at "Between Laboratory Outlier Tests", and Cochran's test is one of them.

Also, how would I calculate the Cochran Critical Values for a given set of laboratories and replicates?

View 3 Replies View Related

Test If Array

Apr 25, 2007

I am currently working on code that uses either arrays or ranges. I often resize local array based on the dimensions of the input. However, I am running into trouble because arrays use ubound for dimensions, but ranges use ether rows.count/columns.count or ubound on value2. How can I determine whether a variable is an array or a range. the IsArray function happily passes a range variable, so that doesn't work.

View 2 Replies View Related

Test For Date

Jul 25, 2007

I have a defined range Picku_time and this function fails
=isdate(range("Pickup_time"))
it gives the =#Name?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved