Im trying to create a formula that check to see if the value in H4 equal any value in the vlookup table m4:m48, if so, then Y, if not then N. the following does not work: =IF(ISNA(MATCH(H4,M$4:M$48)),"N","Y")
View 2 RepliesI'm using a formula to lookup names within text and return that name to a separate column if it's found.
The formula is =INDEX($E$1024:$E$1026,MATCH(FALSE,ISERROR(FIND($E$1024:$E$1026,E1010)),0))
I understand index and match functions, but I'm confused regarding the use of FALSE as the lookup value and the iserror(find( usage.
I am trying to look in B1:B100 for the Cell that matches A1 and C1:C100 for the cell that matches A2 then return the value from D1:D100 that meets the 2 criteria.
View 6 Replies View RelatedI am trying to use a formula similar to VLOOKUP() to look in a table for two values and if both values are found, return another value.
Sheet2 is an example of my output file, sheet one is a generated file. i would like the vlookup formula in sheet 2 cell D2 and down to be something like this,
=VLOOKUP(A2,Sheet1!A:K,10)
But I would like to lookup A2 and B2, and the row that contains both values, return the value in the J column
If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".
The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).
I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.
Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
I have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........
I currently have the below formula to search for text of interest (A1) within each cell of a column (B6:B30).
=IF(ISERROR(MATCH(A1,$B$6:$B$30,0)),"NO","YES")
The formula works perfectly if there is only a single value in the cells. But not so well when the cell has multiple values separated by a comma.
For example:
Column B
X
Y
Z
T, X, Y, Z
It would return a "Yes" for X, Y, and Z because those are found individually. However, T will appear as a "No", because it is found within a cell that is separated by commas.
i have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.
Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:
A B C D Results:-
1 class1 class2 class3 class4 Gg Hi Fr Sp
2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE
3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...
the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
I've been given a spreadsheet that is poorly constructed but I'm not allowed to alter.
The layout is:
Box Name Box Date ID1 ID2 ID3 ....
Where there is not a fixed number of IDs in each box
(There are five different sheets so I'll have some fun concatenating results)
Given a list of IDs I need to return the box name and box date that the ID's file sits in. The spreadsheet has blanks everywhere and the numbers contained in rows are non-consecutive and smaller values can appear later in a column than larger ones.
I think I need some combination of index, offset and maybe sumproduct? TBH, I avoid sumproducts like the plague, preferring to utilise sumifs and dynamic named ranges since this improves clarity so I'm basically a newb at them.
Any skeleton framework that you think would work to return the text values for the LHS where the number could be anywhere? Once I can see a rough example I should be able to get to grips with the logic and wrangle it into the necessary solution.
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
I am trying to automate the account classification of credit card (cc) expenses. The problem is charges to USAirways will always have random numbers after the vendor name on the cc download. I set USAIRWAYS as a Travel Expense and a simple Vlookup will not work.
For example, let's say a data dump in column C of the CC:
cell C2 is USAIRWAYS 037232A49429
CeLL C3:
USAIRWAYS 037282hf39647
Cell C4:
Facebook A8476H4
In column E I have my simple vendor name. Column F as the Vendor Account. For example E1 is USAIRWAYS. F1 is Travel Expense. E2 is FaceBook. F2 is Marketing Expense
In cell D2 & D3 I want to return Travel Expense anytime the formula sees USAirways in cell C2 returning the answer in column F after matching w/ column E. In cell D4 I want to return Marketing Expense.
how do I search the whole text of one cell in a particular column to another cell in a different column? I then want to return the full contents of the cell that contains the text I want to find. Ie. if I want to search the text "fidelity", cell A1 in column B which contains a cell with text "fidelity asset management". I want to return fielity asset management.
View 7 Replies View RelatedI have a database of customers that are all sorted by a customer number.
I have a form that has a number of fields that display customer info. When I use a blank of this form and put the customer number in it's correct field, all the other info fields do a vlookup on the customer number in the database file and return the info, such as phone numbers, name, address, etc.
Here's my question;
How can I check for a blank (using ISBLANK, I assume) to check to see if the customer number exists in our database and, if it's NOT blank, operate on it with the following example which is used to return their insurance expiration date.
=IF((VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))
I realize that this may be a sledgehammer approach and that Access is probably a better tool, but my company has not shelled out the $$ for Access...yet.
I need to look within column A (which contains text strings), match label in column c (which contains text for label), and return result within column B (titled category label).
Essentially, I want to label my data based upon a specific element found within column A with a label list in column C.
I have attached an excel file that shows the current problem. Nested if(isnumber(search( statements can perform this, but I yield too many arguments error after three labels?
Isn't there a way to do this with Vlookup or Hlookup?
(1) I've created a list in A2, which looks up F1:F27. I'd like to place an IF or Vlookup function in B2, that returns the corresponding cell from range G1:G27 based on selection in A2.
(2) Also, is it possible to include the fill coulour based on selections in A2 and B2?
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
I have a spreadsheet!
I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).
What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.
Example:
Sheet 1 - Films
How To Apply Bridal Makeup
How To Fight A Donkey
How To Write Excel Formulae
Sheet 2 - Tags
Channel / Tag / Keywords
Tech - Microsoft - windows vista xp word excel
Tech - Computers - internet pc
Tech - MP3 Players - iPod Zune
So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
Is there a formula to look in multiple arrays and return whatever it finds i.e. text or numeric values?
Sheet1 ABCDEFGH1Cat0 CatFive Donkey32 Mouse2 Wolf43
4WolfFour CatFive Donkey35 Mouse2 WolfFourSpreadsheet FormulasCellFormulaB1=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(A1,$D$1:$E$2,2,0),
VLOOKUP(A1,$G$1:$H$2,2,0)))B4=LOOKUP(REPT("z",255),CHOOSE({1,2,3},0,
VLOOKUP(A4,$D$4:$E$5,2,0),VLOOKUP(A4,$G$4:$H$5,2,0)))
I'm trying to write a formula which will return postcodes from a list of descriptions which aren't consistent in their layout.
For example, I need this to happen
UB3 3NQ - APR13
SW3 5RQ - APR13
Jul 12 - apr13 accrual - ME9 4FW
Mar 12 - apr13 accural - SO14 7P2
Returned to another column as,
UB3 3NQ
SW3 5RQ
ME9 4FW
SO14 7P2
The issue I'm having is that the postcodes aren't in the same place in order to use LEFT, RIGHT or MID functions, and they aren't always proceeded or followed by dashes or spaces in the same way.
I need the returned postcodes to come back in a uniform way so that any duplicates are grouped by the relevant pivot table.
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 Replies View RelatedI have a sheet call Paste and one called look up -
On the lookup tab there is a list of accounts (the numbers of which varies perday) in column B - Using VBA I wish to do a vlookup on tab Paste looking in Column B and returning Column Cs value -
I have tried a few Codes that I have found online but in all cases whenever an account is not found the cose just stops rather than moving to next account
This formula works perfectly except when worksheet 2 has a code that is not on worksheet 1, then I get the error #N/A. I understand why I get the error, but not how to fix it. Obviously some sort of nesting - but what and how?
What I want on worksheet 2, column I - if there is a code on worksheet 2 but not on worksheet 1 - a return of "N/A"...
I'm using VLOOKUP to search for results in another worksheet. What I need is 0 returned if the search fails. Currently I'm getting #N/A and that's causing my other formulas to fail.
View 8 Replies View RelatedI am trying to compare a single cell's Value in 1 worksheet to try to find a match in another column (or a range of cells). If I find a match I want the result to be a 1 and if it is not found then a 0.
Is this the Lookup function?
I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description:
On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found.
Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results: =IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))
I know I can do this by nesting a load of if statements but I was wondering if there is an easier way.
If in cell A2 I have the value 12 and I want cell A1 to return a value FOUND if any cell from A6 to A24 has the value 12 in it.