Can I Combine VLOOKUPS In A Formula

Jan 29, 2009

Basically i need to type in a formula in sheet 1 under each of the months that will match the account name and location from B1 and C1 to the data range in sheet 2 (Columns B and C) and return whatever value is listed on sheet 2 under column E. My gut tells me I need to use multiple look ups but I've tried this and got numerous errors.

View 9 Replies


ADVERTISEMENT

Combine Multiple Vlookups In One Formula

Sep 20, 2008

I am using this formula
=IF(SEARCH("pipe",A2),VLOOKUP(C2,Sheet2!A4:B12,2))
to look up a reference code for "pipe". The reference codes for "pipe" are in column 2 of a table on sheet 2 and are dependant on the dimensions of the pipe given in column 1 of the same table. C2 in the formula is where the dimensions are given. (hope that's clear ).

What I want to do is to extend the above formula to look up column 3 of the table on sheet 2 if, for example, A2 contains "Bend", i.e., =IF(SEARCH("Bend",A2),VLOOKUP(C2,Sheet2!A4:B12,3)).

Combining those two formula using "=if(and " worked for me for the first test, but changing the contents of A2 from "pipe" to "Bend" results in a #Value type error.

I can't use if A2 = "pipe" because extra words could be introduced into A2, e.g., "copper pipe" - therefore the equals would not be appropriate.

View 9 Replies View Related

Combine Sheets Using VLookups And Pivots?

Dec 8, 2013

I have 3 sheets in my workbook, DataA, DataB, and DataC

Data A contains:
ID_customer
and 4 variables A-D

DataB contains
ID_Customer
and 4 different variable W,X,Y,Z

Data C contains
ID_customer
and other 4 different variables

Request: I need to analyses the relationship between the variables using a Pivot table.

My thoughts so far: I think that I need to combine all the information onto one sheet first and then use the pivot table function, but its currently unable to combine onto one sheet, due to Id_ customer numbers appearing several times in DataB and DataC (and not always the same number of times in both), I need to retain all the information from DataB and DataC

I have attached a sample of my data : DataA.xlsx

View 6 Replies View Related

Two VLookups In One Formula

Feb 14, 2012

I want to vlookup one value into two different tables and return the 2nd column in the respective table it finds the value in. if it does not find the value in either table, return "". Currently I have the formula below, but it is not working 100%.

=IF(E6="Shipped","Printed",IF(NOT(ISNA(VLOOKUP(C6,Master!$GP$4:$GQ$5000,2,0)=1)),"Printed",
IF(NOT(ISNA(VLOOKUP(C6,Master!$HD$4:$HE$5000,2,0)=1)),"Ready","")))

View 1 Replies View Related

ISERROR Formula In Cell That Has Multiple Vlookups?

Apr 18, 2008

My problem is when i get a product that isnt in the reference sheets (ie. in cell I19), so when that happens i get "#N/A". I know i can add in ISERROR into my formula, but every time i've tried this it hasn't worked so im missing something...

View 9 Replies View Related

Combine Formula Which Reduce The No. Of Additoanl Formula's Columns

Apr 9, 2009

I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.

To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.

Is There any formula to show fail subject in Remarks column without creating 5 additional columns?

View 14 Replies View Related

Combine Text Entree In Formula With Formula?

May 21, 2012

Is there away to Combine a Text Entree in a Formula with a Formula

=IF('VEGAS - Main'!Z9=1,"MIXED DBLs","")

I am trying to do something like this:

=IF('VEGAS - Main'!Z9=1,"TEAM EVENT"+'Mixed Doubles'!P9

Result will be MIXED DBLs 1st Place

View 5 Replies View Related

Formula: Combine The First Letter Of The Name

Mar 12, 2009

I have 2 cells, one has a first name the other has a last name for instance:
A1 = Joe
B1 = Smith

I am trying to combine the first letter of the name in A1 with the next 3 letters in B1 so that C1 = jsmi

View 2 Replies View Related

Formula To Combine Row Number?

Jul 1, 2013

I have a formula that computes the row number in B58. I would like to combine the row # in the formula after M_MAN!A1015 for eg M_MAN!"&B58&" instead of having to type this. I can then use the foormula to copy the balance of the formulas

=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))

Workings  BC57Row #Quarter5810150Spreadsheet FormulasCell
FormulaB58=ROW(HC_MAJOR_EXP)C58=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))

View 2 Replies View Related

How To Combine Text+value In Formula

Jun 16, 2008

I want the user to select a currency pair in cell B4, eg, EUR/USD

Then cell R4 will look to see if there is any data in B4 and, if so, return the following value into Cell R4....

=MT4|BID! & cell B4 & "m"

the cell b4 is the bit i am not sure about.

View 9 Replies View Related

If Formula To Combine Two Lookup Conditions?

May 23, 2014

Combine two lookup/IF formulas:

[Code]......

So if formula 1 conditions aren't met then it by default would result in formula 2 conditions being met.

View 5 Replies View Related

How To Combine Two Search Formula's In To One With OR Function

Jan 23, 2014

Formula 1 : =IF(SEARCH({"spinning"},A2),"AUTOGEN")

Formula 2 : =IF(SEARCH({"typing"},A2),"Manual")

I want to combine above two formula's with "OR" function.

View 3 Replies View Related

Combine Countifs And Offset Formula

Mar 3, 2014

I have a problem with a cumulative countif and offset formula that I am unable to solve. See the attached spreadsheet for an example of the dataset I get out from our systems.

I want the formula to be fully automatic, so when I change the month in column K2, it will automatically count all the different outlets in column B based on criteria in column L3 and downwards. The dataset will be updated monthly obviously.

I believe that it could be solved with the usage of a countifs + offset formula or something similar.

The main idea of the spreadsheet is to track the movement of customers between different chains. It is thus important that customers that show up in January will not be counted again in February or March, thus creating an unique countif list based on a given month.

Number of outlets example.xlsx‎

View 12 Replies View Related

Combine An If And Or Formula For Text Values

Aug 19, 2009

I have a column say column A that holds text. If the values match say:

ABC1 or
ABC2 or
ABC3

And also match column B if it shows "Yes" then show "Correct" or "Not Correct".

View 3 Replies View Related

Way To Combine The VLOOKUP And IF Statement Formula's

Jan 26, 2006

Is there any way to combine the VLOOKUP and IF Statement formula's into one
formula or is there any other kind of formula which does both?

View 9 Replies View Related

Vlookup In Combine With An Array Formula

Nov 27, 2008

I'm looking for a formula to return the correct value without using any help-column and it should be typed in no more than one cell.

View 10 Replies View Related

Formula To Combine Date And Time

Aug 23, 2012

Is there a formula that can be used to combine a date and a time that will be formatted correctly?

For example if I have the following values:

A1: 08/03/12 (Date)
A2: 16:45:39 (Time)

And then I want A3 to be 08/03/12 16:45:39 as the Date/Time. Can this be done with a formula?

View 2 Replies View Related

Combine 2 Strings Text Into One (IF Formula)

Jan 28, 2013

I have two columns each column has a combination of the same 3 words "cash", "card", and "trade". The are formatted as "Cash & Card & Trade" or "Cash & Card" or "Card" ect. I want a formual that can combine the entries written into each columan into one column. I will put an example below.

A
B
C
D

1
Client
new
old

both (formula in this column)

2
name 1
Cash & Card & Trade
Cash & Card
Cash & Card & Trade

3
name 2
card
cash
Cash & Card

[code]....

View 3 Replies View Related

Combine ISNA And ISBLANK In Same Formula

Mar 5, 2014

How to combine ISNA and ISBLANK in the below formula such that it returns empty cell if vlookup cell is blank.

HTML Code:
=IF(ISBLANK(VLOOKUP(B4,'C:UsersArulDesktop[Common Spares 1.xlsx]Sheet1'!$B$1:$L$77,2,0)),"",VLOOKUP(B4,'C:UsersArulDesktop[Common Spares 1.xlsx]Sheet1'!$B$1:$L$77,2,0))

View 5 Replies View Related

Concatenate- Combine All The P.O# With A Formula Automatically

Mar 13, 2009

Is there a faster way to combine all the P.O# with a formula automatically, no matter how many P.O# there are? Its unpredictable on how many invoices will get pay per wire.

Log # Amount P.O. Numbers 1-a 45,678 1234 1-b 89,173 5678 1-c 66,526 9123 2 22,113 2345 3-a 66,654 6789 3-b 88,650 2468

1 201,377 1234/5678/9123 2 22,113 2345 3 155,304 6789/2468

View 10 Replies View Related

Combine Subtotal With SUMPRODUCT Formula

Nov 10, 2006

I am trying to combine a subtotal formula in sumproduct formula but end in error. Appreciate if someone can assist me with the right formula. I would like to combine subtotal in the following sumproduct formula:

=SUMPRODUCT(((C3:C9)=C14)*((G3:G9)=B15)*(H3:H9))

View 5 Replies View Related

Combine Index And Frequency Into One Formula

Feb 13, 2007

im using this formula =FREQUENCY(Sheet1!B1:B100,A2:A11) to calculate the the frequency of my data from B1 to B100. I would like to know how i can insert the Index formula into the formula above so i can get the frequency of the first 100 rows even if i add rows.

View 2 Replies View Related

Combine Vlookup, Isblank, Isna In One Formula

Sep 27, 2009

What i want to do is to look up of the value of home and away games seperatly. if there is no match for the lookup i get the "N/A" and if there is no value i get "0". If i get 0 the formula will calculate as a lost game.

View 10 Replies View Related

Concatenate Formula To Combine Two Cells Of Data

Sep 8, 2012

I am attempting to use concatenate to combine two cells of data, but to only display the information if both cells contain information.

Currently using the following simple formula:
=concatenate(a1,a2)

However, I only want it to work if both a1 and a2 cells contain data. If only a1 contains information, but a2 is blank, I want it to calculate a blank cell.

View 3 Replies View Related

Excel 2010 :: How To Combine A Formula And Text In Same Cell

Jun 20, 2014

How do I combine a formula and text in the same cell in excel 2010.

What I am looking for is the sum of a number of cells, followed by text.

e.g. something like =SUM(E4:E50)"/35"

If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'

How do I write this formula?

View 3 Replies View Related

Formula To Combine Cells To Build Email Address

Oct 16, 2008

I have a worksheet with three columns. First Name, Last Name and email address. I need to combine the first name with the last name and add our @ourcompany.com to the end of it for the emaill address.

I've figured out how to do that, but my problem is, we don't use our first and last names in the email address. We use first initial and last name.

I know how to make Tom Jones become tomjones@ourcompany.com

What I need to know is how to make Tom Jones become tjones@ourcompany.com.

Can someone point me in the right direction here? I just need the first initial of the first name and combine that with the last name and then add in the @ourcompany.com.

View 3 Replies View Related

Formula To Show Combine Answer And Text In 1 Cell From 2 Different

Oct 27, 2009

i need t oknow if i can have text added into 1 cell with having a formula with a answer in it as well,,,i have attached a sheet with better examples of what i mean.

View 3 Replies View Related

Formula To Combine Multiple Formulas Into Single Cell?

Mar 12, 2014

I'm looking for a way for Excel to combine formulas together into a more compact form for me.

Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.

Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?

Here is the one-cell version of a formula I work with.

[Code] .......

That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.

Attached File : Excel Formula Combination Question.xlsx‎

View 7 Replies View Related

Formula To Combine Legend Values Based Of Where 1's Are Coded

Aug 18, 2009

In this file i did paste special values so the links are no longer there, but in my original file alot of the values you see in the cells are pulled from other worksheets ( thats just so you know to lookin values of the cells and not the formula ).

I basically need row 18 starting from Column O to self populate.

Its based off of which has 1's in the Column. I have the completed here manually just to show you what i am looking for . I usually have to go to row 3 and Enter the data manually depending on which 1's are filled in for the column.

If i could have a method of having row 18 starting with Column O to self populate based off those 1's

View 8 Replies View Related

Excel 2003 :: Formula To Combine Delimited Numbers

Dec 11, 2012

I am using Excel 2003 and trying to create a formula that will combine comma delimited numbers from two or three cells in the same row and output them to another cell in the same row as comma delimited numbers, sorted with duplicates removed.

So the data is something like this ...

Cell A1 ... 1,5,8,19

Cell B1 ... 4,26,1,8,8501

Cell C1 ... 1,6,1301,12

I would like to consolidate these into cell D1, sorted and distinct ...

Cell D1 ... 1,4,5,6,8,12,19,26,1301,8501

View 1 Replies View Related







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