Questions tagged [vlookup]
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.
501
questions
0
votes
1
answer
2k
views
Displaying multiple columns using VLOOKUP in Excel
Lets say in a worksheet, lets call this SHEET_2, I have student information.
On another sheet, lets call this SHEET_1, I want to be able to lookup a students ID number and have all the students ...
user669017
1
vote
0
answers
26
views
How do I copy a row of values to another tab if a specific criterion is fulfilled?
I have two tabs with identical tables except one of them is supposed to contain those records that matches one of two values in one of the columns.
What I want is that whenever I add a new row to ...
1
vote
2
answers
272
views
Excel Index Match or Vlookup and copy
I have a sheet 1 with 3 columns as shown below. I have another sheet named sheet 2 with only the first names in Column A that are the same and found on sheet 1 column A.
I would like to write a ...
- 33
0
votes
1
answer
430
views
Unexpected Behaviour using VLOOKUP in OpenOffice Calc
I am using VLOOKUP in OpenOffice Calc on Windows and trying to use a lookup table to populate some other cells.
My Lookup table ranging A1:B6 is
Car 4 Wheels
Florida No
Bus 8 ...
- 15
0
votes
0
answers
53
views
Excel Vlookup- adds up percentages with duplicate relationships
Please refer to the image below. This data has one column called entity ID and anther column, Parent entity and then the Percentage column for each relationship. Looking at the data I see there are ...
- 15
0
votes
0
answers
781
views
VBA vlookup error
There are about 1200 rows with data after the first part of the script has run. Afterwards I want to run a vlookup for column 6, which has the initials of the person handling the problem, against a ...
0
votes
1
answer
196
views
Using a Column Name in a VLOOKUP
I have a spreadsheet that takes data from the other 52 spreadsheets and totals it.
The columns in the sheet correspond to the names of the other spreadsheets (using dates, i.e., 01/01/2017 column ...
- 103
0
votes
1
answer
475
views
Compare Data from one worksheet to another and fill in the match
I need some assistance with Excel 2010. I would like to find a quick way to assign tickets, but not change previous ownership.
For example: I would like to compare my current list of unassigned ...
0
votes
2
answers
1k
views
Aggregated Function over VLookup on Range
Is there a way to get a maximum value of a range of values using standard Excel functions?
More Info
I want to find the maximum value in a range of cells. A1: =max(A2:A10)
However, I don't want to ...
- 840
0
votes
1
answer
2k
views
Microsoft Access to Excel reference (like VLOOKUP)
I am considering converting a business customer database to Access as it has gotten quite large and unwieldy otherwise. One concern that has cropped up is accessing the data from Excel. For example, ...
1
vote
1
answer
100
views
How can I match values based on criteria?
I have two tables with various columns in an Excel sheet. In these two tables I have two columns (one per table) in which I have some alphanumeric values.
For example,
table 1 col A:NAMES: AB34092, ...
- 13
2
votes
3
answers
1k
views
Excel - vlookup with priorization
I have an Excel sheet with the following data:
-----------------------------------------
| Unique line ID | ID | Status |
|---------------------------------------|
| 1 | AA | ...
- 21
1
vote
0
answers
1k
views
FAST VBA based vlookup equivalent
Trying to copy the function of vlookup in VBA, the best I can come up with is this:
Function VBALOOKUP(LUcell As Variant, LUArray As Range, LUColi As Long, OutColi As Long) As Variant
Dim Y() As ...
- 754
-1
votes
2
answers
1k
views
Excel if cell from two different sheets match delete line
I have two different sheets, query and formonth, in same workbook. Columns b on both sheets have the data i want to compare. If column b on formonth matches any cell in column b on query sheet, i ...
0
votes
1
answer
55
views
Excel Vlookup #N/A error
I need the cell to show blank if the following conditions aren't met but excel is returning an #N/A value.
=IF(ISBLANK(O14),"",(IF(AND(C14<>"OFF",$B$2="Week 1"),...
0
votes
1
answer
13k
views
How do I use VLOOKUP with today's date or latest date?
I have a table with dates and their data, but the dates column don't have any formatting and is written as yyyy-mm-dd, e.g. 2016-08-29. It's aligned to the right. This table is generated from a Google ...
- 23
0
votes
2
answers
114
views
Vlookup or VBA on getting specific data out of many single cells
I have a strange one. I extract data from an application into an Excel spreadsheet to do some high-level analysis. I have one column, called History, and it can have numerous entries inside a single ...
- 147
1
vote
0
answers
290
views
Excel: VLOOKUP with cell containing multiple references
I have a table which contains multiple references per cell separated by a blank space.
In an other table that contains one line per reference, I want to use VLOOKUP to retrieve the name of the ...
- 123
0
votes
1
answer
138
views
Microsoft Excel vlookup formula
I have two sheets as below.
Sheet1:
Sno Emp ID Name Address
1 101 X. ?
2 01 Y. ?
Sheet2:
Sno Emp ID Name Address
1 10 D. XX
2 101 X. ...
- 3
1
vote
1
answer
223
views
vlookup + if command
I am a newbee excel user and need assistance in compiling an Excel formula. I have basic IF functions down and Vlookups. I need assistance in combining the 2.
I need the formula to read: if CELL L2 ...
1
vote
1
answer
2k
views
Referencing a dynamic, named range between workbooks
I inherited this data structure. I have a bunch of products and their supplier information laid out as so:
Supplier Order Number Name Cost
ACME 00123 A 1
ACME 00321 B ...
- 320
0
votes
1
answer
741
views
using an Array formula to do VLOOKUP in multiple sheets
I have used below formula in this file but I do not know why it doesn not work and return #N/A!? Infact I need to lookup "Station .No" from other sheets to display it in Sheet1 :
= VLOOKUP(I3,...
- 173
5
votes
2
answers
40k
views
In Excel how can I search for #N/A cells?
I'm trying to go to the first instance of a #N/A cell, the result of a VLookup that failed.
I know I can conditionally change the value when the result is #N/A, but what I want to do is just locate ...
0
votes
1
answer
8k
views
Excel - Keeping Original Cell Value with VLookup and Iferror function
I am stuck with a Vlookup formula I'm working with. I need to apply the vlookup formula to a whole column, but here's the catch - I need the formula to not do anything if it returns an error. There ...
- 13
0
votes
1
answer
207
views
How to optimize fields that has "if" function which return blank value on Excel?
Here is the screenshot, it's only an example since I can't post the original one:
I made this worksheet to cross-check the employee present with another sheet that has information whether this ...
- 111
1
vote
0
answers
760
views
Why VLOOKUP () doesn't work in this example in LibreOffice Calc?
This is a simple working example:
A column has these names:
اسم عربي واحد in A2
اسم اجنبي اثنان in A3
اسم ثالث ثلاثة in A4
اسم رابع in A5
The second column B2 has these numbers in front of the ...
- 153
0
votes
1
answer
271
views
VLOOKUP assistance: match column with column on another sheet
I have a fairly simple VLOOKUP Question but I'm comically horrible with Excel.
Basically, I have two spreadsheets. I'm wanting to find data from Column B of Sheet 2 on Column W from Sheet 1. If the ...
2
votes
1
answer
1k
views
Using INDIRECT within VLOOKUP
I am conducting a series av tests of data in different excel workbooks. It order to not have to change all Workbook names in the formulas used I find it suitable to use INDIRECT() and refer to a cell ...
- 23
1
vote
0
answers
996
views
What's VLOOKUP's true algorithm with range_lookup set to TRUE?
I have not been able to find Microsoft reference that explains the true behavior of VLOOKUP with the range_lookup setting set to 'TRUE' (which means finding an "approximate match"). Their reference ...
- 111
0
votes
0
answers
149
views
Two identical values not treated equally in Excel
Was trying to use a simple VLOOKUP on a table with some calculated values (using formulas). It couldn't do exact match, even though the lookup value is equal to the table value. But copying the needed ...
- 365
-1
votes
1
answer
97
views
vlookup - Is it possible to use vlookup to pull specific info like this?
I am trying to pull the following from each group of Numbers -
CHG000509443 05-16-2016 22:48:41 for the Requested
CHG000509443 05-17-2016 13:14:35 for the Approved
Note 1 - It is always the first ...
- 147
4
votes
2
answers
395
views
Excel vlookup reference purposely missing shows #N/A - stop it
I have a cell which does a vlookup.
But the table to which it refers is always changing and when the specific value is there is shows fine.
But when the value isn't there it shows #N/A - how can I ...
- 41
6
votes
4
answers
34k
views
Excel INDEX MATCH Checking Multiple Columns
The problem I'm essentially trying to solve is a VLOOKUP that is checking Columns A:E for a value, and returning the value held in Column F should it be found in any of these.
With VLOOKUP not being ...
1
vote
2
answers
196
views
Storing numbers as text for VLOOKUP
I am aggregating a large amount of data from several workbooks and need to cross-reference several thousand items, perfect job for VLOOKUP.
However, all I get is the lovely #NA error.
The issue of ...
- 13
0
votes
1
answer
83
views
Issue to extract data without Vlookup
I have trouble extracting data from a huge file. Until now I was using Vlookup but of course it only takes the first number it finds.
I need to be able to read data in each columns = X,Y,W,Z for the ...
- 31
2
votes
1
answer
156
views
Excel Vlookup Error for Some of My Text
I have an Excel report with web page titles that I am trying to match corresponding page views to using VLOOKUP. The lookup table is a separate Excel file exported from Google Analytics.
When I ...
- 21
0
votes
2
answers
996
views
How to make excel SUMUP values of multiple rowkeys in a two-dimensional lookup to return sumed up values in a corresponding cell
So I have this challenge with a set of data arrangement and by merging the VLOOKUP and MATCH strings I was able to get my result but there is a little hiccup!
Pasted below is the merged string that ...
- 3
0
votes
1
answer
3k
views
Need to extract multiple matches from a list of values
I am trying to extract data from a worksheet using an array formula but it will only return one (the first it finds) value. The formula I am using is:
=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($...
1
vote
1
answer
3k
views
VLOOKUP for latest non-empty cell
Basically, I've got the following spreadsheet.
There are dates along the top. I do a certain amount of past papers each week, and record the highest score I get in a week in cell (paper, week). The ...
- 13
0
votes
2
answers
315
views
Look up table values in different worksheets to create summary (VLOOKUP, HLOOKUP, INDIRECT)
I'm having problems creating a summary cashflow spreadsheet that looks up a couple of references - a date (which is no problem) and a different spreadsheet name (which is the problem).
I have ...
- 1
1
vote
1
answer
148
views
Look up specific word in a table array and return adjacent cell
I would like to know if there is a way to use the Vlookup function to find specific words in a table array.
E.g
Table array:
Code: R2 Color: Blue
Code: R3 Color: Red
Code :EC-R2-01-2016, ...
- 11
1
vote
2
answers
251
views
In Excel, how can I make a row of data in a table that has specific text in one cell automatically appear in a table on a seperate sheet?
I download a report with 8 columns of data into a table each week. The amount of data changes each week by row (one week might have 40 rows, the next might have 100 but no more than 150). The data ...
- 11
0
votes
2
answers
2k
views
If cell contains "Specific Text" return value of cell beside it. Similar to VLOOKUP
I have 2 sheets, one with a list of parts(sku) and all the vehicles that the part will fit on (fitment) separated by "^^" it looks like this:
sku | fitment
1 |car1 ^^ car2 ^^ car3
2 |car2 ^^ car3
...
- 103
0
votes
2
answers
372
views
Explanation for a VLOOKUP formula used in a Conditional Formatting rule
I have the following VLOOKUP formula for a Conditional Formatting rule:
=VLOOKUP(A1,Sheet1!$A$2:$C$17,3,FALSE)<>0
This works fine and does what I want, which is this: If column C (3rd column) ...
- 486
6
votes
2
answers
9k
views
Range mapping in Excel
I have a column of data in Excel with numbers [0..100]. I need a new column, which maps these to a new set of values such that:
> 90 : 4
80-90 : 3
70-79 : 2
60-69 : 1
< 60 : 0
I attempted to do ...
- 225
0
votes
1
answer
291
views
Excel 2013 vlookup & sum
I have a data set which shows test results, each person my have several rows of data. My labels are
ID - Test Date - Test Result - Interval Between Tests
I want to sum the interval between tests ...
- 1
0
votes
1
answer
6k
views
How to populate information from one sheet to another in Excel Workbook?
My current task is to create a formula where my information from one sheet will auto populate in another sheet when the product description is copied.
To be specific, we need to have the items ...
0
votes
1
answer
41
views
Using a Date to refer to a page to VLOOKUP into
I currently have data that has a date and item number and I'm looking to pull the price from a different sheet.
=Vlookup(#,Jan1!(Range),2,0)
Gives me my price of Jan 1
=Vlookup(#,Jan3!(Range),2,0)
...
- 3
0
votes
1
answer
351
views
Vlookup in Excel returns #VALUE
I am using two worksheets in Excel but am having problems with using VLOOKUP.
Sheet 1:-
ID CATEGORY_ID
Sheet 2:-
CATEGORY_ID CATEGORY_NAME
I essentially want to add the CATEGORY_NAME in ...
- 105
2
votes
1
answer
361
views
VLOOKUP and IF in multiple excel sheets
I have two excel sheets, as below, in a workbook
Sheet 1 & 2:
Below is the relation of fields between the two sheets
EID is same as EmID
DepID is same as DependentID
DepDOB is same as ...
- 23