ISNA And Vlookup (get Data From Another Tab)
Oct 20, 2008
I'm trying to get data from another tab, but only if it is greater than or less than a certain number. Here's what I'm using but doesn't seem to work.
=IF(ISNA(VLOOKUP(A13, 'Aim MTD'!A2:T63, 5, FALSE)),">=22.00%",VLOOKUP(A13, 'Aim MTD'!A2:T63, 5, FALSE))
A13 is targeting the name of the person
Aim MTD is the sheet tab the info is in
And I want to select the information only if the cell value is 22% or greater. If the cell value is less than 22%, I don't want it to read or to read zero. I'm not an excel expert (yet), but hope to be one day!
View 5 Replies
ADVERTISEMENT
Jul 17, 2009
I use the following formula. It works except it returns "0" if the field is blank. What alternate formula can I use that will return blank instead of zero if the field is blank?
=IF(B3="","",IF(ISNA(VLOOKUP(B3,Customers!$A$2:$L$1502,5,FALSE)),IF(ISNA(VLOOKUP(VALUE(B3),Customers!$A$2:$L$1502,5,FALSE)),"",VLOOKUP(VALUE(B3),Customers!$A$2:$L$1502,5,FALSE)),VLOOKUP(B3,Customers!$A$2:$L$1502,5,FALSE)))
View 9 Replies
View Related
Nov 6, 2012
I'm trying to use this formula but I get the error "too many arguments". How can I do the same as the formula without an error:
=IF(ISNA(VLOOKUP(B12,GYROLOK316ANDM!A12:E2000,3,FALSE)),VLOOKUP(B12,GYROLOKBR!A12:E2000,3,FALSE), VLOOKUP(B12,VALVESFILTERS!A12:E2000,3,FALSE),VLOOKUP(B12,GYROLOK316ANDM!A12:E2000,3,FALSE))
I added in the VLOOKUP(B12,VALVESFILTERS!A12:E2000,3,FALSE) - when I used it without that it worked. But I have 3 sheets I need the the VLOOKUP to look over and this formula that I copied off another thread only had 2 tabs.
View 2 Replies
View Related
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
Aug 23, 2008
I have the following vlookup working exactly as I want it to in excel: =IF(ISNA(VLOOKUP(LEFT(C2,10)& "*", NAME,2,FALSE)),"",(VLOOKUP(LEFT(C2,10)& "*",NAME,2,FALSE))) -NAME is a defined section on another worksheet
The problem I have is when I come to use this within a vba macro I'm writing the wildcard section automatically gets spaces added so it goes from "*" to " * " and excel doesn't like it! The code I'm using to write it into the cell is simply:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)),"",(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)))"
how to stop the spaces being added?
View 2 Replies
View Related
May 21, 2013
I have been using an IF,ISNA,VLOOKUP formula as follows which I am sure you are all familiar with :
=IF(ISNA(VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0)),"",VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0))
This formula works correctly, displaying the lookup value for K7. My query is between the"" I can place text to display when K7 is blank and this works correctly too. However I would like to place a formula in here. The formula is VLOOKUP(I7,Orig!A7:B35,COLUMNS(B7:B35)+1,0 i.e. the lookup value is now I7 and not K7 when K7 is blank.
I have tried the following and variations based on what I know but they return errors.
=IF(ISNA(VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0)),(""& VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0),VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0))
Any better way of using I7 as the lookup value when K7 is blank.
View 7 Replies
View Related
Oct 28, 2008
=IF(ISBLANK(VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0)),"",VLOOKUP(1,'D-Sort-R'!A96:$N$450,4,0))
I've almost got what I need for a mind bending series of sorts that go from worksheet to worksheet. I need to add ISNA along with the ISBLANK above.
View 9 Replies
View Related
May 10, 2006
I realize there are a number of threads on this topic, and I've read through them but have been unable to apply those answers to this problem. I've attached a sample of the worksheet in question. The worksheet is supposed to tell me if messages are supported via a certain Network (BFX).
Column A is the result of a VLOOKUP function, which returns Yes or No if the entry is found, or #N/A if the entry is not found. Column B is my attempt to translate column A into strictly "Yes" and "No", converting an #N/A into a "No". For some reason, the ISNA( function I'm using doesn't seem to work, as highlighted in row 18.
View 2 Replies
View Related
Jan 26, 2009
I have 2 spreadsheets. Both have a list of part numbers on. Beside each part number i want a formula that tells me if the part number is present in both spreadsheets so its says TRUE. If its not present then FALSE. I've tried using this command but it doesn't seem to work properly =ISNA(MATCH(B2,A:A,TRUE))
I've attached the 2 spreadsheets. Spreadsheet1 is where i need the forumula placed beside the part numbers so if its present in Spreadsheet2 as well it displays TRUE beside the part number in Spreadsheet1.
View 4 Replies
View Related
Nov 4, 2008
It then places all the data on a new sheet. My problem is that some of the formula that the macro places on the new sheet come back with the result #N/A - which is fine, as they are the result of a lookup.
What I want to try and do, is at the end of the macro, get it to delete the row if the activecell is showing as #N/A
I've tried to write a small bit of code for it...
View 10 Replies
View Related
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
Dec 21, 2006
Let me first explain what I tried to do, before getting to the mess I created. Maybe I should use another formula?
I wanted to compare the amounts in two files on the basis of the same product number. Thus search for a sheet and a column in another file for the same value of A6 (current file) and show the value of the same row in another column (of the other file). However, if there is no value I wanted it to display zero (0) or a text such as "False".
First I did a basic LOOKUP which should work?. However it still showed #N/A for cells without a reference value (A6 is THA) or target cells without a target value (€100.00). I tried to avoid this by combining the ISNA function. However, although the last value shown is correct, the ones before change and seem to add up, or refer to another cell?
Another issue I did not come to yet is the fact that I would like it to search for the correct sheet based on the first three letters of the reference (A6), preferably not case sensitive. A6 is Tha13, first three letters indicate sheet name Tha. Any suggestions on this? ...
View 9 Replies
View Related
Feb 15, 2010
Am trying to figure out the best way to combine into 1 cell an ISNA(Match... and a Lookup array that will run if the ISNA(match ... returns a false value.
Right now it's running in 2 separate cells ISNA(Match ... and then an if statement that if the ISNA(Match returns false, that the look-up runs, if it doesn't return false, then the value is 0)
View 9 Replies
View Related
Apr 2, 2009
I have configured conditional formatting to check valid entries in a dependant list. I used the solution provided by SHG at:
http://www.excelforum.com/excel-prog...ant-lists.html
Now I need to use a macro to activate and deactivate the conditional formatting due to performance issues.
When the formula for data validation includes the indirect funtion I got a compile error and the macro won't run.
View 3 Replies
View Related
Oct 29, 2012
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
View 4 Replies
View Related
Jul 13, 2009
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies
View Related
Oct 5, 2009
I have tried nested ifs and vlookup to compare two sets of data and change the first set of data if it is than the first. But leave it alone if it either is the same or does not exist in the new set of data. It sometimes seems to work but i find it is not consistant. It looks simple but i think i am missing something.
if column A has identifiers and column B has results then it should work if the identifiers in column C are found in column A and it looks to see if column B and D are the same, then change B if different but leave it if either it is the same or not there.
A
code1
B
36
C
code1
D 33
View 9 Replies
View Related
Aug 3, 2009
On J7:J500 there is a list of names
James
Mark
Momeena
On A7:A500 there is a list of name with duplicates
James
Mark
Mark
Mark
Momeena
Momeena
Momeena
Mark
I need a vlookup
Range needs to be set from a7:a( a highlighted row)
Vlookup will cross ref values from J column against A (using the target range)
Then will add up column d that have the same name
So for instance
You have
A
James
Mark
Mark
Mark
Momeena
Momeena
Momeena
Mark
D
50
50
50
50
10
10
10
5
J
James= 50
Mark= 50+50+50
Momeena=10
View 14 Replies
View Related
Apr 23, 2009
I have attached a sample. I want to return a 'Team Number' to column C. The data is from another sheet, however in the sample I put it in column H and J. Should be simple VLOOKUP but columns won't match exactly.
View 9 Replies
View Related
Sep 13, 2009
I have a thread in here called "Inputing data values automatically based on data value in another column". I have determined that I need to use the VLookup function.
View 3 Replies
View Related
Apr 26, 2013
I have a problem to use vlookup in excel. I have an excel to run macro and other than i have two excel. One of them is list just a column which includes SAPkeys of people it looks like
SAP Key
xxxx
yyyy
xxxx
and i have another excel which i use as database. in this database sap keys of all people are written in "C" column. and there are other information about that person is written in "D", "E" and "F" columns.
what i want is bringing all information belong to sap key is written in different sheet. it would be like that after the macro run
SAP Key Name Location
xxx john london
yyy
zzzz hanks berlin
View 6 Replies
View Related
May 8, 2014
There is data which comprises of Part (#), Discount (%), Start Date & End Date. In this data Part (#) can repeat but Start Date & End Date periods will not overlap.
I want to write a formula which looks up into this whole data set and give me result as TRUE or FALSE and get Respective Discount (%) in another cell. True if for a specific Part (#) Discount (%) is applicable on Current Date.
Attaching sample excel file for example of data set.
View 7 Replies
View Related
Jul 18, 2014
I have the following sitation:
xls Sheet 2
ITEM EQUIPMENT
1234 a
1234 b
5678 c
5678 d
xls Sheet 1
PO ITEM EQUIPMENT
56 1234 **
99 5678 **
** in the equipment cell of sheeet 1 I want a drop down (data validation) with the possible equipment I can use forthis item only. I.e. for PO 56 Item 1234 only the equipment a & b should be in the drop down menu.
View 12 Replies
View Related
Jan 6, 2009
I am currently building a data sheet using VLOOKUP. When there is no data to lookup it returns #N/A, which is fine. The problem is when I go to total these columns because of the NA it returns NA in the total box. Having tried the if isna formula I am now returning #VALUE!. I may be doing the IF ISNA formula wrong.
View 4 Replies
View Related
Jan 7, 2009
I have a problem with VLOOKUP not extracting the data I need from 1 workbook.
I want to populate 4 different sheets in the "108" workbook with data from the "1st" workbook. I've uploaded the workbooks to show the formulas I have already. The data in "1st" workbook changes daily and can have upwards of 2000 rows. I've simplified the list for the example.
In the "108" workbook I want to fill in the first three columns with the formula down to about row 500.
I've tried different formulas to do this using EXACT, MATCH, and VLOOKUP but to no avail.
View 9 Replies
View Related
May 7, 2009
i want to set vlookup but have spaces in the start of source and destination data is there any help for trailing this from vlookup. i have upload the sheet. i also tried vlookup("*"&.............) but not working
View 3 Replies
View Related
May 24, 2014
I am using the code below to put a data validation list into A2. How do I code in the vlookups into that so that a query can be looked up based on that value?
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,2)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,3)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,4)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,5)
=VLOOKUP(A2,Table_Query_from_MS_Access_Database,6)
I have put the spreadsheet here on box.net as it is too large to attach:
[URL] ..........
View 2 Replies
View Related
Nov 26, 2009
I have this spreadsheet with a list of products and their components in the sheet called "product_list" this information is downloaded from a plant pc in this format. to make it more readable and easier to edit i've made the "mix formula" sheet that using vlookup finds the product by product code and brings through only the relavant information. The problem is if you want to alter this information you have to try and find the relavant cell in the "product list" sheet. What would be easier is if you can edit the relevant info in the "mix formula" sheet and it would change the data in the "product list" sheet. Is there a way of doing this? I've attached the spreadheet with the relavant data to demonstrate.
View 2 Replies
View Related
Mar 1, 2006
Can I get VLookup to ignore certain data.
My situation is this,
Sheet 1 has
ColumnA Column B
Name Job#
Bob 1234
Bob 1235
Bob 1236
On Sheet 2 I have a combo box so I can pick any name from column A on sheet
one, then I want to use Vlookup, or whatever will work, to show me the Job
numbers that a selected person has worked on. So when I pick "bob" from my
combo box in A1, B1:B3 would display 1234, 1235, 1236.
View 10 Replies
View Related
Jan 27, 2012
I have a table simmilar to this:
Reptile Dog Red
Reptile Tiger Purple
Mammal Tiger Stripes
Mammal Tiger Spots
Mammal Racoon Black
Mammal Cat Plaid
Mammal Dog Brown
Mammal Dog Purple
Reptile Dog Red
Reptile Tiger Purple
Tree Dog Orange
Tree Pine Green
I would like to use this data to populate within a seperate worksheet that reads:
Mammal, Tiger, Stripes
Mammal, Tiger, Spots
Mammal, Dog, Brown
Mammal, Dog, Purple
Essentially pulling all of one duplicate item within a column. The problem I ran into is when I run a vlookup within the entire table, it gave me duplicates.
Basically, it gave me...
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal Tiger Spots
I know this is because of the array, just forwarding to the next item, and rerunning the lookup... since Mammal is not at the top... it has to wait till the array gets to the area of "mammal".
How do I create the list, so it will not create the duplicates... like in the example I gave.
View 2 Replies
View Related
