VLookup is a Microsoft Excel function that searches for a value in the left-most column of a table array (a range), and returns a value in the same row based on the column number from which the user wishes to retrieve the value.

learn more… | top users | synonyms

1
vote
1answer
21 views

VLOOKUP not interpolating all cells?

My VLOOKUP formula is not interpolating all cells AD AE AF 11-6-2014 15:20 90,4 #N/A 11-6-2014 15:21 89,1 #N/A 11-6-2014 15:22 90,4 Good 11-6-2014 15:23 89,1 Good AF column is my ...
0
votes
0answers
19 views

Excel 2D-lookup on a PivotTable with changing coloumns and rows

I want to do a Vlookup on a 2D pivot table but neither the coloumns and rows of the pivot are fixed and are subject to change due to ever changing data source. How can I perform a accurate vlookup on ...
0
votes
1answer
13 views

EXCEL MAC: VLOOKUP copying the date instead of the data

First of all, am sorry if this was already posted by someone else, I tried searching for hours now, but did not find something similar to what I have here. I am very newb on excel. My problem is, I ...
1
vote
2answers
37 views

How to get VLOOKUP to return the *last* match?

I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be ...
-1
votes
0answers
14 views

i want to vlookup data from multiple excel fine in 2007 without opening the file

I have created a dashboard which seeks data from multiple files. I need a vlookup formula where the path and filename changes as per user pc.. the data to seek is of State, Company, brands and its ...
0
votes
1answer
22 views

Excel: How to find a range, from a list of ranges, that a value is in?

I hope someone can answer my question! I have lots of values and need to know which ones lie within a list of ranges and if so which range do they lie in. For example: worksheet 1 contains my ...
0
votes
0answers
30 views

Vlookup from formula in Excel 2010

I would like to use a vlookup, where the value being looked up is the result of a formula, however my formula below results in an error. It works if I type in the result of the formula, but not for ...
0
votes
1answer
70 views

Excel SUM From Different Sheets IF Date Found

I have a workbook with separate sheets for each product (about 20 sheets, adding more on a regular basis). Each product is only available for a certain time frame, and has daily sales data recorded on ...
0
votes
2answers
379 views

Searching for a value in Excel, returning yes or no

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO". ...
0
votes
1answer
46 views

In Excel selecting an item from row when cell matches the first item in that row

I have a LIST of 5 optional sizes. There are several numbered mating parts that coincide with each of the sizes forming a pair. Any 1 of the mating parts can go with a chosen size, but only 1. Some ...
0
votes
1answer
54 views

Convoluted Date Lookup

I found this, which is pretty close, but I don't know VB, so I'm trying to do this with a vanilla formula. First worksheet has a series of tables of unemployment rate: 1 per US state. Each table ...
1
vote
0answers
32 views

Trouble adding values from different ranges in excel

I have a fairly unique problem I am having trouble with. Essentially, I need to fix column U on Sheet 1. Ignore stuff out of the tables, as most of that is stuff I either will hide but reference, or ...
-1
votes
1answer
77 views

With VLOOKUP, if N/A, keep original value

I'm using Vlookup to find a value, but when it doesn't work, it returns N/A. How do I keep the previous value instead of having NA. I have found the answer HERE How do I implement this? Do I just ...
0
votes
0answers
60 views

=VLOOKUP() function performed on dollar value displays value without “$”

I'm not entirely sure how to present pleasant-looking spreadsheet representations on SE, so I would appreciate any advice on that issue given on the side. I want the B2 cell to display the salary ...
1
vote
1answer
70 views

In Excel 2011 on Mac, how to return a non-blank matching value with vlookup?

I have a list of domain names and their registrars on one sheet, and the same list of domains and other data (but not registrars) on another sheet. I need to get the registrar name from sheet A, to ...
0
votes
2answers
61 views

MS Excel using multidimensional lookup

Hi am trying to assign a value given that if I have a some value that matches column 1, it will search try to check if for that specific row my my product variable is lesser than specified. So let's ...
0
votes
3answers
77 views

How to use =VLOOKUP under these circumstances?

So I know how to use =VLOOKUP when you are searching for a single value within a cell, but I'm not sure how to do it when I have a range, ex. say the cells value is 1-5, the next is 5-10. This is ...
0
votes
2answers
40 views

vlookup with blank look up value

I have a Vlookup running and I want it to return 0 if the look up value is empty. example: =(VLOOKUP(A6,F:G,2,0)+1)*C6 +-------+--------+-------+-----------+-------+-------+ | | | ...
0
votes
1answer
106 views

Count instances of a letter in a row if a certain cell in that row contains a specified date

I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date. I ...
0
votes
1answer
218 views

Formula to display text from multiple cells from one sheet in another

I have a list of sales reps and the names of the customers they've sold product to, on one sheet. I have tried VLOOKUP, but it will only return one set of text from the cells. I would like to know, ...
1
vote
2answers
91 views

Excel: return name of column with highest value in a non-continuous array

I have looked up many answers to find the highest value in a column, and return the column name. However, every answer is dependent on using a continuous array and uses the INDEX function. This is ...
0
votes
1answer
97 views

Converting Monthly data to Daily data on EXCEL for MAC

I have data columns of the last day of each month with a value. Apparently, the value of the entire month is stationary. I have to make a new column with daily dates while having the particular ...
0
votes
2answers
398 views

VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg. I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...
0
votes
1answer
68 views

sum values based in agent ID

I have a sheet with agent id's in column a and under each ID is a sub identifier with a value in column b of 1 - 7 sub identifiers are Break_1000 Meeting_2000 Training_3000 Personal_4000 ...
0
votes
3answers
263 views

Lookup range of words in range of sentences

Based on the following example: I want to look up for words from the $D$2:$D$6 range in sentences from the $A$2:$A$8 range and use the value from the side column (here Tag). So far I'm doing this: ...
0
votes
1answer
278 views

Excel 2013: VLookup for cells that share common characters within cell but are both surrounded by other non-matching text

I am pulling information from 2 different databases. The databases use different naming protocol for the exact same item/specified placement however they always have certain components of the name in ...
2
votes
1answer
105 views

Excel VLOOKUP using results from a formula as the lookup value

I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have ...
1
vote
1answer
135 views

Excel access.Vlookup? IF?

What im looking to do, is move stuff from one excel sheet, to another sheet in the same workbook. We will call the sheet with the data the "suppliers list" and the sheet where i want it to go, the ...
1
vote
1answer
186 views

How to join two worksheets based on the data they contain

I would like some help on how to join two different worksheets that I receive every week. Sheet 1 contains information on the defects that were observed during a week (#defect, type of defect, ...
0
votes
1answer
66 views

vlookup datetime FALSE; return value from 1 of two columns based on exact datetime

Based on data below, I am trying to do a vlookup such that if the datetime in ends in 18:59 return the value in column_3, if the datetime ends in 6:59 return the value in column_4... 10/7/2013 18:59 ...
-1
votes
1answer
62 views

excel reference- VLOOKUP formula error

VLOOKUP is great except every time i try to input the formula, i get an error message, "you may not use references to other worksheets or workbooks for calculated column criteria" anyone know how to ...
0
votes
1answer
1k views

Lookup list dependant on the contents of another cell

hope someone can help to crack this one? I've got a table on my data entry tab (sheet 1) where each row includes two data validation drop down lists (source is on sheet2). I want the options in the ...
-5
votes
1answer
10k views

How do I compare two columns in Excel to find if there is corresponding data?

How do I compare two Excel sheets to detect whether the data in column B of sheet 2 appears in column A of sheet 1?
1
vote
2answers
2k views

Excel VLOOKUP by second column using table name as range

Using the example table below, I can use the formula =VLOOKUP("ABC123456",Table1,3,FALSE) to lookup the Demand value, but I want to do be able to perform the lookup by using the Cust Part field ...
1
vote
1answer
54 views

Excel fomularbar shows correct Value, Sheet shows VALUE error

Here is the data I have been using, data format all General: Name Address Adam 250 Mohame 150 Adam 220 Adam 423 Yasir 240 Ibrahim 236 Adam 242 Index formula cell B11 contains the ...
0
votes
2answers
389 views

VLOOKUP when VLookup Value is a cell populated via another formula

I keep getting a return of NA when trying to accomplish the following: formula: =vlookup(left(Q2,3),A2:C20,3,false) Definitions: Q2 is a cell that is populated from a formula. Its calculated value ...
0
votes
1answer
68 views

How to use match & index or lookup function based on a number derived from a time calculation

I have created a gantt chart to use as part of my rota system. I want to be able to use a formula (either LOOKUP or MATCH & INDEX) to look up the hours worked against another sheet and pull in the ...
0
votes
1answer
228 views

VLOOKUP within VLOOKUP

I'm trying to build a quick form to allow a user to calculate certain data using a data table. I am using LOOKUP and VLOOKUP to achieve this. This is my current formula: ...
0
votes
1answer
513 views

Excel matching between combination of columns

I have the following data on an excel : Sheet1 : id effectiveDate rateValue CrTime 20 20130627 0.08 6/28/2013 23:05 20 20130626 0.1 6/28/2013 6:23 Sheet 2: ...
1
vote
1answer
742 views

Excel: VLOOKUP and INDEX+MATCH return #REF

I am trying to make cells in one sheet look up a name in another sheet, and return the value one column to the right of the cell with the name. In itself, a perfect job for VLOOKUP. But, when I ...
2
votes
1answer
54 views

WHERE in vlookup?

I'll try my best to explain this - I know exactly how to do this in SQL but not in Excel. I have two worksheets (let's call them ws1 and ws2). In WS1 we have a 'product code' column and in WS2 we have ...
1
vote
2answers
112 views

Forcing consistent format for lookup values and lookup arrays

I have this annoying issue where I need to look up values in a column that contains both numeric and alphanumeric data. For instance: 1234567 P654321 7654321 V234567 ..and so on. This column is ...
1
vote
1answer
84 views

Excel #NA error returning TRUE

I have an Excel formula that I use to check against duplicates that is not working. The data is not in order, which is why the VLOOKUP was needed to match values. ...
1
vote
2answers
146 views

Working with “~” in excel

I have a data set that has names that contain ~ within them. Unfortunately, I cannot find a way to filter or incorporate these cells in a formula. For example, I tried to use a text cell that had ~ ...
0
votes
1answer
81 views

Merge multiple tables by comparing multiple rows

I have multiple three column tables that share two rows in common: DailyCosts: DailyPrices CodeRole CodeRegion DailyCost CodeRole CodeRegion DailyPrice 1 1 ...
0
votes
1answer
713 views

vlookup and indirect and external shared workbooks

i have two excel workbooks that are both shared in our company folder. excel1 is where each employee has his own page to fill information and column 1 of each page is the row reference excel2 is a ...
0
votes
1answer
254 views

Excel vlookup map to network drive (not mapped to H,N so on) [closed]

I'm trying to use use vlookup to access another excelsheet on a network drive. Currently I have a vlookup that works fine =VLOOKUP(A2;'H:\CVO\[test.xlsx]Sheet1'!$A$1:$C$4;2;) However I cannot ...
0
votes
1answer
160 views

Vlookup not working as expected for soccer data

i have a vlookup function that is supposed to give me some data from another worksheet. the worksheet contains soccer data, specifically about the european cups of the past 3 years (9-10, 10-11,11-12, ...
0
votes
1answer
301 views

Merge rows and join matching values

I have two sets of data on the first set there is one column that has a list of IDs like below. I have another set which could have several rows matching that same ID like below I am trying to ...
1
vote
2answers
10k views

How do I prevent vlookup returning 0 for values that are blank in the lookup data? [duplicate]

My vlookup call in an Excel 2010 spreadsheet is returning a value of 0 for values that are blank in the lookup data. How can I force vlookup to return a blank when the data is blank, and 0 when the ...