I am struggling with the Vlookup function on Excell 2003. I am trying to lookup data with refernce to two cells to return the data. for example cell A1 = 1 AND cell B1 = 2 then return column 3 data. However I need this in a lookup or something very similar.
View 8 RepliesSheet 1 has one column
Customer Number (unique)
e.g.
111
222
333
444
Sheet 2 has two columns
Customer Number
Email Address
e.g.
111-----billgates@msn.com
111-----billgates@microsoft.com
111-----billgates@hotmail.com
222-----davidbeckham@mufc.co.uk
222-----davidbeckham@fa.co.uk
333-----me@test.com
444-----you@test.com
Now, when I do a vlookup on sheet one to obtain all email addresses for customer 111, it only returns the first email address - e.g. billgates@msn.com
Ideally, I would like;
Is there a function that can achieve this?
If not, can the multiple occurrences be returned in one cell, separated by;?
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 RelatedSo i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.
I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
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 RelatedI am running a Vlookup on a large set of ID#'s that exist accross multiple servers.
The data is structured in (2) columns how you see below , and you will notice that the same ID# can show up on multiple servers.
However, the VLOOKUP is only returning the first match it finds.
Assuming that I want to keep the data structured as is, how do I make the vlookup return all matches?
ID# LOCATION
23 SERVER1
34 SERVER4
23 SERVER2
13 SERVER1
17 SERVER3
34 SERVER1
i am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).
I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:
is there anyway to have a vlookup formula display a blank cell instead of #N/A if the value it was looking for is not found. For example the first spreadsheet contains the persons name say Bob, the 2nd spreadsheet that the vlookup formula is searching does not contain the name Bob right now excel shows #N/A but what I want it to show is a blank cell. I've tried conditional formating to just white out the text if #N/A appears but can't seem to get that to work.
View 7 Replies View RelatedI have a Vlookup, it returns a number, and when it doesn't find a match it returns N/A, and when it does return a match and the return value cell is blank, it returns 0. Is there any way I can have this so when it doesn't find a match, it returns a blank cell, and when it finds a match and there is no value in the return cell, it returns a blank cell as well, rather than 0?
View 6 Replies View RelatedAfter using a vlookup to find a value in a cell I want it to return the cell belows information.
View 2 Replies View RelatedSheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I have a normal VLOOKUP of this loookup range:
A 1
B 2
C 3
D 4
=VLOOKUP(T14,U6:V9,2,TRUE)
If the value in T14 is in the lookup range, I get the expected value returned. If the value exceeds the vlaues in the lookup range (e.g. I enter "E"), I get the last value in the range (4). How do I get it to return a blank cell if the entry in T14 is not found?
I think I am surrounded by trees and can't find the forest, I have a list of tariffs and need to get them to a generic name. The attached file may help, the gist of it is I need to output the generic name for each tariff instead of the supplier name for that tariff.
View 11 Replies View RelatedI tried searching for a solution to my formula problem in this forum regarding vlookup and saw similar formula e.i returning multiple values but it doesn't cater with what I need.
Vlook is returning only the first value but what I need is to return all the multiple return values in 1 cell. I've attached a sample worksheet for reference.
OK for example if i hae this list of data:
Invoice number Order number
111 560
112 561
112 562
113 563
What i need to do is a vlookup from the above table to return the following:
Invoice Number Order number
111 560
112 561, 562
113 563
Traditionally the vookup will return the first value it can match. I can return multiple values on different lines using an array formula but this is not useful.
In the example below I have table array A1:B8. Lookup criteria in cell A11 and want to return MULTIPLE values from column B in cell B11.
I would prefer to not use VBA if possible
A
B
1
[Code]....
This is probably really basic but can anybody tell me how to return the colour of a cell aswell as the content in a vlookup??
View 9 Replies View RelatedIs there a way to get the vlookup function to return the cell address, rather than the value, of the lookup?
View 9 Replies View RelatedI 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
I need to compare a string from say A1 to a range of cells in another workbook and if found return the value of a cell on the same row.
Something like - look up string value in A1 in other workbook.sheet1.A1:A65535 if matched return the value of matched row column b.
OR
IF Otherworkbook.sheet1.A20 = A1 return the value of B20
i have a excel 2003 sheet that needs some vlookup formulas
3 columns in total
employee ID Emplyee name Team
i have worked out how to create a dropdown list and return the emplyee name to a cell using vlookup on the emplyee ID column
I have also worked out how to create a dropdown list and return the employee Id to a cell using vlookup on the employee name column
i really want to return all the members of any team into a single cell but when i create a dropdown list it lists multiple entries for example team1 and only returns a single member to my deignated cell
code1
code2
lookup Range1
lookup Range2
Return Value
Lookup
Multiple Return
[Code]....
I am trying to vlookup multiple value and return multiple values in one cell
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies View Relatedi have a column of data part number part text e.g 7w1,8b1 in the next column i want to write a function that will look at the other and dependant on whats in it place a word e.g. for 8b1 i need it to look at the "b" and enter beauvale if theres no "b" but a "w" enter wollaton, i have tried an if function and used "*b*" but it doesnt seem to like wildcards ....
View 9 Replies View RelatedRather 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 RelatedIn 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)
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.
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
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.
Possible to have two spreadsheets and then perform the following tasks
1. Check If the cell in column I from cell I4 and the spreadsheet varies in length is blank and if it is - see item 2
2. The data equals certain values i.e. CDE or CDE - QUO or CDE - PRO - see item 3
3. Perfom a VLOOKUP to another spreadsheet
If the routine stops at any of the items then move to the next cell
Data is on spreadsheet - Allocations.xls
Vlookup is performed on this spreadsheet - 001 - Allocations - Blocks
The Vlookup for example is: =VLOOKUP(A5,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536.9,FALSE)