VLOOKUP: Range & Return
Jan 10, 2007
Q1: In the range for the lookup I'm performing, the column with the possible match is the 2nd column. I've tested my function with this range, but it fails (range is $A$x:$K$x, with possible matches residing in column B). If I change the range so the 1st column has the desired data ($B$x:$K$x), it works. Can I modify the function to search using the 2nd column so I don't have to rearrange the columns in my worksheet?
Q2: Can I return an entire rold of data? If yes, how? I only know how to return one cell.
View 14 Replies
ADVERTISEMENT
Aug 31, 2007
I wanted to lookup value A5, and return all columns in the range A1:D5, I could use the formula =vlookup(A5,$A$1:$D$5,2,false) and copy this formula 3 times changing the third parameter 2, to 3 , 4 and 5. i.e 4 VLOOKUP formulas.
I could, instead, convert the VLOOKUP into an array and enter it CSE i.e =vlookup(A5,$A$1:$D$5,{2,3,4,5},false). This returns the right answer but "appears" to have only used 1 VLOOKUP.
What if this was extended to say 26 columns so instead of "hard coding" the array constants I used =vlookup(A5,$A$1:$D$5,COLUMN(B:AA),false). Is this better than 26 indivisual VLOOKUP's in term of speed?
View 9 Replies
View Related
Jul 11, 2014
Data set
Column A Column B
PriceCode1 5.36
Sheet 2 Data Set
Column A Column B Column C Column E
PriceCode1 5.02 10.01 313
PriceCode1 10.02 50.01 314
PriceCode1 50.02 100.01 315
Formula on Sheet 1:
{=INDEX('Price calculation'!$E$1:$E$70,MATCH(C3&D3,'Price calculation'!$A$1:$A$70&'Price calculation'!$B$1:$B$70,1))}
The issue I am having is that the value isn't an exact match so it is returning line 3 with a value of 315 rather than line 1 and a value of 313. What am I doing wrong?
View 1 Replies
View Related
Jan 22, 2014
I need to lookup to search a range on a different sheet and return the appropriate corresponding cell.
Basically its if a1 is found in the other sheets range a1:a100 then return the corresponding Bcolumn value from the different sheet.
Formula
=VLOOKUP(A2,'All Users'!A$2:A$100,'All Users'!B!2:B!100)
Example of 'All Users' Sheet
A B
STAFFIDSTAFFNAME
24177John
10487Paul
20031George
84087Ringo
85772Pete
24485Stuart
3829George
51836Yoko
View 3 Replies
View Related
Jan 25, 2010
I am trying to use a VLookup to pull a value out of a range and return my intended data. See attached spreadsheet. The data I'm pulling from is in the top chart. The bottom chart, in the factor column, is where I want the data to pull to. Ex) If the "Months of Development" column in the bottom chart =13, I want it to pull the factor value from the top chart where the month is 13. The problem is that the top chart has a range of values in the "Month Range" column of 13 - 24. I don't know how to get the VLookup to recognize this and pull the same factor into the chart below whether the value is 13 or 24. Maybe VLookup isn't the best option here.
View 2 Replies
View Related
Feb 18, 2010
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ).
The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me
The problems I need solving are:
I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this?
I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
View 9 Replies
View Related
Aug 27, 2012
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
View 3 Replies
View Related
Oct 29, 2008
Rather than explain, it's best to look at my simple example attached. Why am I getting #N/A in cell E6 when I want it to return "one"?
View 3 Replies
View Related
Nov 26, 2008
In C1 I have =OR(B10="MOODY",B10="AIB")
PROBLEM:
B10 (see below) is a Vlookup and it is preventing C1 to return the name of
the agency: AIB or MOODY - Instead I get TRUE or FALSE, No use to me.
Hours Agency
70.00 AIB
80.00 MOODY
40.00
40.00
40.00
40.00
40.00
40.00
70.00 AIB (B10)
View 2 Replies
View Related
Feb 2, 2010
I'm trying to use the VLOOKUP function to return value from a row below.
I have a large list of numbers (sheet Data) for the entire year with 8 different items per week, and I need to create a list for each week with group of 8 unique items as shown on "Week 21" sheet (I did it manually by copy and paste, highlights are just for the example, don't need to be at the original file). After the first 8th rows, it should start over until it gets to the last value on sheet "Week 21".
Basically, Column B on sheet "Week 21" should be statics, and once I change value on column A it should return values as in the example automatically.
View 8 Replies
View Related
Mar 21, 2014
For some reason when i run a vlookup, it is only returning the first lookup value for the whole column. Not sure why this is happening.
See attached example. vlookuperror.xlsx
View 6 Replies
View Related
Mar 19, 2009
I have a spreadsheet with 2 tabs.
Tab #1 is a complete list of ALL possible pipelines
Tab #2 is a partial list that will ONLY have a pipeline listed if there was activity for the month
I need a Vlookup on Tab#1 that looks up the activity from Tab#2.
However, when a pipeline is NOT populated on Tab #2, I'm getting "#N/A" on Tab#1.
Instead of seeing #N/A - I need it to say "0" so that I can sum the column total.
I tried to look at Error Types...and found that the error type for #N/A is 7, however - I can't figure out how to make the formula work.
View 6 Replies
View Related
Nov 17, 2012
I am trying to use vlookup to locate a cell in another work book. The identifier is in the same workbook as the vlookup formula however the table and column to look within is in another. The identifier will also show up in the other work book where the table resides.
View 4 Replies
View Related
Jun 8, 2014
I have this formula "=VLOOKUP(B5,$F$254:$G$258,2,FALSE)" I want it to return a "0" when cell B5 is not populated. How could I do that? Right now it returns "#N/A" and messes up my math down the sheet..
View 3 Replies
View Related
Dec 9, 2013
Been working on this problem for a while, I need to have a vlookup (or other function) return locations based off of a concatenate (for unique identifiers). Only issue is that the concatenate formula creates doubles which returns the same location for each vlookup. I have attached an example file.
View 2 Replies
View Related
Jan 15, 2009
I have 2 columns of data: Column A is basically postcodes, but just the 1st part and Column B is a #2. If somewhere else on the sheet I type in the postcode IE. SW19 it will show besides it in my lookup the number 2. However if I type in a postcode that is not listed in Column A, I'd like to to return the value 1. I cannot figure this out. My code is:
View 3 Replies
View Related
Mar 25, 2009
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 Related
Mar 4, 2013
I have a formula that needs to return blanks as blank and 0s as 0. The vlookup formula will return blanks as 0, or I can make it return a blank whenever it is zero (below), but some cells may have a 0 record that need to return 0s, not blank.
Right now I am missing a few records that are entered as "0". I have:
=IFERROR(IF(VLOOKUP(O16,'Sheet1'!$A:$Z,8,FALSE)=0,"",VLOOKUP(O16,'Sheet1'!$A:$Z,8,FALSE)),"")
View 5 Replies
View Related
Aug 26, 2007
i have a customer work book for outstanding orders of which there are hunderds of rows with there name order number and the items that are outstanding
when i have progressed all my purchased orders i take all the items and run it throught my work book using vlookup to return who wanted what items if any
but what if there are more then one customer wanting the same item how can i get vlookup to know that and return the next match
i'm still very new at excel so can someone please help me
attached is a small test of my work book
if you see item 3182Y/1.0/BLK
you can see it is wanted by both a.f switchgear and amg
but it will only return a.f switchgear
View 14 Replies
View Related
Jan 9, 2009
I don't even know if using the IF statement is the right function, but anyway. I am using VLOOKUP to complete a supply list, but of course if the supply name isn't on the first spreadsheet then the result is NA. Instead I want it to return 0.
View 3 Replies
View Related
Aug 5, 2009
Is it possible to write a vlookup that will return a value of Yes if the value is true and a value of No if the value is false?
I have data in column A on sheet 1 so the lookup value starts in cell A2 and the table array is on sheet 2 in columns A and B so the column index is 2.
View 9 Replies
View Related
Feb 18, 2011
Here is my starting Vlookup Formula I. I need to alter it as I will state after the formula...
Code:
=Vlookup(A2,REPORT!$A$2:$D$10000,4,0)
Instead of returning the data from column D upon the first match I need the smallest available number to be returned. There will be more than one match and they will all have different numbers to return so I need the smallest return from D.
So in "REPORT" there is many duplicates in Columns A but they all have different values in Column D and I need to return the smallest number from Column of all of those duplicates in Column A.
View 6 Replies
View Related
Jul 25, 2012
When I use my vlookup function and hit enter, the no value shows up and I can still see the function syntax that I wrote in the cell. Here's a sample of what my sheet looks like:
Number
Name
Hazard
Number 2
[Code]...
I need to know the value in the 'number 3' column is for the 'number' column. For example, I need '1302-76-7' inputted in a column adjacent to one of the first three columns. Another example: I need 'na - 07' inputted in the same row that has a 'number' of '1073'. This is how I want it to look:
Number
Name
Hazard
Number3
[Code]...
When I type in the vlookup formula listed above, nothing happens. This is exactly how it looks in my spreadsheet, even when I click another cell or hit enter. Is my syntax wrong or have I just missed something?
View 9 Replies
View Related
Aug 20, 2002
Is it possible to have VLookup return more than one value at a time?
View 9 Replies
View Related
Jan 9, 2007
Is there a way to get a vlookup for return multiple values if there are reoccuring numbers? for example
A 1
B 2
C 3
D 4
A 5
If those were my two columns, could I get the value A to return both 1 and 5?
View 9 Replies
View Related
Jan 20, 2008
I am using the formula : =VLOOKUP(G6,$R$8:$S$16,2) which is fine however i want it to return the value 0 if G6 is blank.
View 9 Replies
View Related
Sep 13, 2008
how to modify the below v looup formula so it returns only the first word instead of the whole value
=Vlookup($A7,DATA,13,0)
I am doing a comparison of locations but one sheet has just the Location, the other data is formated as Location-Address
I would just like to return the location as i have an IF formula which checks if both Match and because of this is returning false
Also ii anther part of my work book i am doing a look up on division, at present it is returning the Division name, However i would prefer it to just return the first 3 or 4 Initials of the look up
for example
if the division called err.. Apples Pears Plums Ltd i would like to return APP
if it were Apples Pears Plums Bananas Limited i would like return value to be APPB
View 9 Replies
View Related
Mar 18, 2009
I'm trying to vlookup age in column A, then find the closest match of that row, then return the top row's column value of that closes match(largest value less than or equal to). looking for a formula to accomplish this.
For example, for age of 1month, I have a value of 7.5. So, I'd like to return: "5th"
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Age (in months)3rd5th10th25th205.1928815.570876.1151876.94590830.56.1719496.5359487.0757587.930901417.0704867.4290337.9697898.84744751.57.9690238.3221178.863829.763994Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jun 9, 2009
I am trying to create a macro and I need it to return the row # based on a vlookup of a date and not 100% sure how to do that.. Hoping to get a little assistance.
I am looking up a date in column A, and need it to return the row #, then I can do the rest of the macro I need. The date I need to look up varies, and is based on the following function I have in a cell elsewhere, but if there is an easier way in vba to write i'm open:
=IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1)
which basically says if it's monday lookup the date from 3 days ago (friday), otherwise look up yesterday's date. So today it returns 6/8/09 and I need to find the row that has 6/8/09 as the value in column A.
Now the dates in column a, are all formulas that say previous row's date + 1, so the only row that has the actual date in it is the very first one, then we just add a day. Not sure if that matters, but figured I'd give all info.
View 13 Replies
View Related
Jun 11, 2009
Which returns 0.03316
When I look at the formulas in B17 by clicking on the name of the fuction and then clicking on the function button, the roundup function arguments shows 0.071. Also the vlookup function arguments shows 0.071. Yet I get the #n/a as the result from the vlookup in B17.
I have tried coercing the formula by addin +0 like =VLOOKUP(ROUNDUP(B15,3)+0,$N$2:$O$91,2,FALSE)
I have also tired *0 like =VLOOKUP(ROUNDUP(B15,3)*0,$N$2:$O$91,2,FALSE)
Here is a kicker... When I replace B14 with 0.9 or 0.7 or 0.6, the formula works. but I get a #N/A on 0.5.
View 9 Replies
View Related
