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
votes
1answer
23 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
7 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
22 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 ...
-1
votes
0answers
15 views

using VLOOKUP for finding differences between two words arrays

Please, help me to figure out how to correctly use formulas (like =VLOOKUP() or =MATCH() ) in order to compare array of words in column A with array of words in column B and a return difference ...
0
votes
2answers
31 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
53 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
26 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
48 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
73 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
49 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
31 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
216 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
47 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
100 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
212 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
90 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
109 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
135 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
58 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
55 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
783 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 ...
-4
votes
1answer
4k 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
1k 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
53 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
322 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
62 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
135 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
312 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
549 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
51 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
83 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
78 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
121 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
79 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
461 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
176 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
145 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
245 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
6k 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 ...
0
votes
1answer
81 views

Copy multiple data from one sheet to other without Multiple vlookup

I have two spreasheet and there are numbers of column on both the sheet, There is one common field (IDs) between those sheets. I can do multiple vlookup but would not like to do so because there are ...
2
votes
2answers
192 views

Excel 2010 - VLookup, Search and Match are doing things beyond my imagination

I have two worksheets, one containing a list of e-mail addresses and information, the other containing e-mail addresses that should be blacklisted. To remove the blacklisted e-mail addresses I thought ...
0
votes
3answers
133 views

Excel Vlookup errors

I am using Vlookup example =VLOOKUP(E10,A18:B50,2) to select a description of a product but it works for some lines but not others. When it doesn't work it tends to select the last descriptions ...
0
votes
3answers
206 views

Excel combining INDEX and MATCH

I have a sheet with the following data: A B 1 Raisa A 2 Mellissa A 3 Emma A 4 Slyvia B 5 Salina C 6 Beula A 7 Apolonia F And I would like to list all people with A's, ...
-1
votes
1answer
72 views

if statement containing multiple lookups

I need to create a formula that has three lookup criteria. I have one source document, called "Sales Force". I want to know if the value in A1 is in column AA of Sales force tab, if not, then look ...
0
votes
2answers
921 views

2 Dimensional vlookup + match, pulling from two different data sets?

here's a tricky one for me, hopefully not for you. I have a data table in one sheet which lists product numbers in the column, and materials used to make said product in the top row, with ...
0
votes
2answers
2k views

Looking-up the values of one worksheet in another worksheet

Basically, I could do this manually, but as the data is huge, it would take a lot of time. I am sure there must be a formulaic way of doing it. I have two worksheets in Excel. Worksheet 1. Column-A ...
0
votes
2answers
2k views

Excel - matching values in columns in different tabs and then pasting into another column if there's a match

I have an Excel worksheet with two tabs, Tab 1 has a list of employees with information on different courses they've taken. Among them is a tab with a 5 digit employee ID to easily identify them.. ...
0
votes
1answer
2k views

returning string from vlookup in excel

I am having trouble with the vlookup function in excel. I have 2 files: file1.xlsx and file2.xlsx. Both files have a list of products with a number of various information and I need to add values ...
0
votes
1answer
986 views

Excel Vlookup in the same workbook but different worksheets

I have a workbook which has two sheets. What I am trying to do is to Update the service entry worksheet, column UpdatedPartID with the CorrectedID from Parts sheet. The corresponding column for ...
2
votes
0answers
246 views

Excel: Why is VLOOKUP returning inconsistent and unexpected results?

I'm automating an Excel column to assign account values based on the vendor paid. For instance, if AT&T gets paid, the account value is Utilities:Telephone, and when the post office is paid, the ...