FAQ Database Discussion Community


Four Columns Match on Different Worksheet

excel,excel-2010,vlookup
Is there a way to find if the combination of 4 column cell values has a match with a different sheet's 4 column cell and display true if the match exists using vlookup? If not what would be the best way to deal with this situation?

VBA lookup for approximate value

vba,match,condition,criteria,vlookup
I want to perform a special VLookup where the value which is found would match two conditions: The invoice number must be the same The value found from Column G must be within the tolerance -100 to 100 Precisely speaking, if the first value found from Column G (e.g. -18,007)...

Excel - Match data from column and get the value next to it

excel,vlookup
Is there a formula that match data, get the value next to it, and then post that value into another cell? This is what my excel sheet looks like: Sheet A Column A | Column B | Column C | Column D ---------------------------------------------------------- Bob John Cat John Sue Dog Sue...

Select value from cell having “today's” date in the same row

excel,date,excel-formula,vlookup,worksheet-function
I have two columns: Date and Price. I want to pick the value of the cell in the same row as today's date: I want to show "today's" price, so supposing "today" is 12 Feb, then for the above example today's price should show 18. How can I do that...

Updating 2 columns from one excel workbook to other workbook

excel,vba,vlookup
I have two worksheets Book A and Book B. Book A has 3 columns Elements (duck, elephant,deer,monkey), Weight(20,70,18,25), Size(10,30,10,6) respectively. Book B has also same columns Elements (elephant,deer), Weight (80,28) and size(40,20) respectively. I need to update weight and size of Animals column from Book B in Book A. like....

Can Not Get My VLookUp In Excel To Return The Requested Data

excel-formula,vlookup,excel-2013
Can Not Get My VLookUp In Excel To Return The Requested Data I am trying to pull data from another sheet based on data selected from a dropdown on the main sheet. All the formatting is "General" =VLOOKUP(F15737,'Location Master'!$A:$J,2,FALSE) It just keeps returning me #N/A...

V-Lookup Meaning

excel,vlookup
I've come to a point where another set of eyes might be best to determine the meaning of my results. What I don't understand is that in column B my results seem to be showing up in the C (Answer) Column. However when I look at the answer column numbers...

Conditional VLOOKUP or INDEX MATCH

excel,match,vlookup
I have to lookup values in a table wich is not in order (and can't order it) where I lookup a number which may or may not be repeated, but I need to bring the data of a specific one. The data table is more or less like this: SAP...

VBA Vlookup within loop

excel,vba,loops,find,vlookup
The user enters usernames in inputboxes ("Enter username"), which appear in the first column from row 4 onwards (specified as cells (4+k-1,1) in the code). The number of usernames he enters corresponds to the number of users he indicates were active on a certain date, specified in the first inputbox...

Two Column Lookup

excel,match,vlookup
I have a data set that I want to return an indexed column using two values: a year and a name. Both these values are formatted to general (I also tried text) in my spreadsheet. In one work sheet I have a like of people: On the other, I have...

Excel: Check if cell string value exists in column, and get all cell references to that string

excel,vba,excel-vba,vlookup
I suspect this may be a job for VBA, which is beyond my abilities. But here's the scenario: Column A in Sheet 1 (CAS1) contains x rows of text values Column A in Sheet 2 (CAS2) contains x rows of text values Part A - For each row value in...

Stata: Keep only observations with minimum, maximum and median value of a given variable

select,stata,vlookup
In Stata, I have a dataset with two variables: id and var, and say 1000 observations. The variable var is of type float and takes distinct values for all observations. I would like to keep only the three observations where var is either the minimum of var, the maximum of...

if the comparison of two columns is true, then compare their corresponding value in the next two columns and report true/false

excel,if-statement,vlookup
Can't seem to get this one. I am comparing in excel if 2 columns (A and H) have any matching document #'s with =NOT(ISNA(VLOOKUP(H3,$A:$A,1,FALSE))) If this returns TRUE, I want to compare the revision # corresponding to each document # and return TRUE or FALSE as well. I have the...

Match and replace operation in data frame in R

r,replace,pattern-matching,match,vlookup
Let's say my dataset is like the following: John NA kaira carry John NA maya Sam maya leo paty leo tinker NA tinker fabo leo maya I have another dataset: John 1 carry 2 maya 3 leo 4 tinker 5 fabo 6 sam 7 paty 8 kaira 9 I want...

Find value and copy entire row to another sheet

excel,vba,copy,paste,vlookup
I'm trying to create a vba code but I'm not succeeding. I want to search on COLUMN "F", for value: "Answered" then copy row from "COLUM B TO F" and paste on sheet "ControlAnswered" lastrow;...

INDEX/MATCH, or another function?

excel,excel-formula,match,vlookup,worksheet-function
I have on Sheet1 18 columns: N a list where an Order Number can be selected O a Line number (as the orders have multiple line numbers associated with them) P, Q and R, I want to pull over the data associated with the Order Number and Line number entered....

Vlookup/Index Function Trouble

excel,indexing,excel-formula,vlookup
I am trying to pull the latest date from a range. That range must match the customer name and whether its live or closed. Column L (Live Date) lists all the dates. Column A (HQ Name) lists names of all the customers which can have multiple lines with the same...

VLOOKUP query help required - count from a range

excel,excel-formula,vlookup,worksheet-function,countif
I'm trying to build a spreadsheet to help automate points scoring for an office F1 fantasy league we have. I've attached an example data set, but basically I need to search a range, then count how many times the constructor appears in the numbered positions (discounting R, D as they've...

Excel VBA code for multiple vlookup

excel,vba,excel-vba,match,vlookup
For a conduit network, I am trying to find the pipes that drain to a manhole. There can be multiple pipes that can drain to a single manhole. My data-structure are organized in the following way: - Stop Node Label ........ ................ - MH-37 CO-40 - MH-37 CO-40 - MH-39...

Select from dropdown list to multiply two cells then input result into another

excel,function,drop-down-menu,vlookup,multiplying
I have a simple spreadsheet for tracking my hours worked and pay, depending on which job function I perform that day. Tech= $10, Video= $20. I've made a dropdown list for my two different job functions in cell "H5". I have my hours worked in cell "G5" and I want...

If cell = #N/A, use another vlookup

excel,vba,if-statement,vlookup
I have this Excel VBA macro which runs really well Sub PCMSLookupTool() Dim LastRow As Long With Sheets("Lookup Tools") '<-set this worksheet reference properly LastRow = .Range("A" & Cells.Rows.Count).End(xlUp).Row With .Range("J10:J" & LastRow) .Formula = "=VLOOKUP(A10, 'PCMS-dump'!A:B, 2, FALSE)" .Cells = .Value2 End With End With End Sub But I...

Comparing cells in one worksheet to columns in another and printing the rows that match in a third worksheet

excel-vba,excel-formula,excel-2010,vlookup
The data I am working with are strings, not integers. I want to take two columns in worksheet1, say column B and D and compare them to two columns in worksheet2, say C and E. If a cell in column B == a cell in column C and a cell...

VLOOKUP is returning blank as 1/0/1990, rather than nothing visible

excel,date,formatting,string-formatting,vlookup
All cells are formatted for dates, when a cell is blank I would like it to return an apparently blank cell rather than 1/0/1900. Here is what I have so far however It is still returning the date instead of a blank: = IF(ISNA(VLOOKUP($B$4,TrainingDatabase!$A$3:$S$14,3,0))= 0,"", (VLOOKUP($B$4,TrainingDatabase!$A$3:$S$14,3,0))) ...

Look up value, on another Excel sheet

excel,matching,vlookup
I have a small list of names with no emails on sheet one, and on another sheet (sheet 2) I have the master list of names with their emails in the adjacent column. I want to look up the email associated with the name from the master list (sheet 2),...

Combine VLOOKUP and SUMIF in one step

excel-formula,vlookup
What I want to do (if possible) is sum each metric of each transaction with a common customer_id in ONE STEP. The output should look like the "DESIRED OUTPUT" sheet. I know some sort of lookup will need to be used to reference the "CUSTOMER LOOKUP" table. I can see...

vlookup split value VBA

excel,vba,excel-vba,split,vlookup
I have created macro that works like a vlookup but has split values. I would like to find value from second sheet of split values (separated by semicolon ) and copy and paste the description to new sheet. The first loop goes through the list in sheet 2 and sets...

VLOOKUP not returning for datetime

excel,vlookup,excel-2013
I have a list of hours when water is used. this list does not generate hours when water was not used. I'll have list like: Hour Liters 5/3/14 6:00 PM 36.288 5/3/14 7:00 PM 15.328 5/3/14 8:00 PM 1.6 5/4/14 11:00 PM 18.752 5/5/14 12:00 AM 21.664 5/5/14 1:00 AM...

sqlite version of vlookup

sqlite,vlookup
is there a way in sqlite to do the equivalent of a vlookup? I'm trying to do something like this: Master table: ValueA | ValueB | concatValueA&ValueB Mapping table: concatValueA&ValueB | mapped Value final table: ValueA | ValueB | concatValueA&ValueB | mapped Value In Excel, I would just do a...

VBA vlookup formula error

excel,vba,excel-vba,vlookup
I am a newbie in excel macro vba. I have a problem on my vlookup code which refers to another workbook selected by a user. Here's my code: Private Sub vlookups() Dim data_file_new As String Dim i As Integer Dim a As String, b As String, path As String data_file_new...

VLOOKUP average a range of cells

excel,vlookup
I have a standard VLOOKUP formula =VLOOKUP($G28,'Analysis 1'!$A$2:$CR$32,$M28+$M28,TRUE) How would I amend this so that rather than just return the answer i would want it to average the result of 3 cells, the cell above the VLOOKUP, the cell below the VLOOKUP and the cell of the VLOOKUP? I would...

SumIf with Strings?

string,excel,vlookup
This may be a stupid question, and if it is, I apologise. I have a table in excel: Column a...........Column b 1 property1.......problem x 2 property2.......problemy 3 property3.......problemz 4 property1......problem a I was wondering if I could use sumif (or any similar formula) to add the problems, referring to a...

VLOOKUP to Delete Values That Don't Match Referenced Values

excel,vba,excel-vba,vlookup
I'm trying to take all of the values held within SourceSheet.Range("C2:C" & LastRowSource) and cross reference them with all of the values held within ReferenceSheet.Range("F7:F" & LastRowReference). If they do not exist within that range, then I want to delete the entire row that holds that value in the C...

VLOOKUP in last column of Table_array

excel,excel-formula,vlookup
I understand that VLOOKUP searches the first column of a table in order to find a value, then it grabs the value from the same row and a different user-specified column. The following code returns data from the 2nd column, column B. VLOOKUP(5,$A$2:B100,2) Is there a way to set the...

Converting letters to text and back again (Excel 2013)

excel,excel-formula,formula,vlookup
For a piece of coursework I have to complete a register of student grades. I am trying to calculate their overall grades by converting their grades from each of the four units into numbers (which I have done using the VLOOKUP function), but I need to then convert the result...

How to handle multi-type variables in Powershell?

powershell,vlookup
I have a Powershell script which runs an Excel VLookUp function through a variable that can be of either String or Int32 type. The script runs great when I declare the $Value variable as either a String or Int32, but how can I deal with the fact that the $Value...

Excel VBA vlookup using Dates

excel,vba,loops,excel-vba,vlookup
I am working with three sheets. Worksheet Start Page has dates from A4 to lastrow. I have a Fund trend sheet with dates from A11 to last row. The vlookup is searching for Dates in the Fund trend sheet based on the list of Dates in the start page sheet....

vba vlookup loop increment +1

excel,vba,vlookup
This should be an easy question for the seasoned pros. 1) I'm trying to offset the active cell down one each iteration of the loop. 2) I can only move down by one because I'm not sure of the syntax available. 3) I was thinking the_result = the_result + 1...

Combine multiple VLOOKUPs

excel,if-statement,excel-formula,vlookup,worksheet-function
How would I combine these together into one?: =IFERROR(VLOOKUP(B2:B11,Sheet2!A:B,2,FALSE),"No Match") =IFERROR(VLOOKUP(B3:B12,Sheet2!D:E,2,FALSE),"No Match") =IFERROR(VLOOKUP(B2:B11,Sheet2!G:H,2,FALSE),"No Match") These are just three but eventually I would need 12 in total to be joined as these are searching team names and there are 12 teams in total....

Excel VLookup #NV error

excel,vlookup,na
I'm trying to make a VLookup in Excel but I get everytime a #NV error. This is table EVENTS: This is table TRACK: the formula on field F2 in table EVENTS is =SVERWEIS(E2;TRACKS!$A$2:$B$52;1;FALSCH) SVERWEIS is the word for VLOOKUP in the German version. FALSCH means wrong...

Read external file match specific string in first column and return respective string of second column in php

php,string,return,matching,vlookup
I have two text files, csvurl.txt and tickerMaster.txt tickerMaster.txt H0001 Remarks: No "H0003" in tickerMaster.txt and the number are not in sequence csvurl.txt H0001, URL1 H0003, URL3 I would like to read the entries in tickerMaster.txt one by one, say H0001, H0003... and createURL by matching the data in csvurl.txt....

vba vlookup keep formatting

excel-vba,formatting,vlookup
My code below has a vlookup which changes a cell in my data (CurrentMonth), but I can't get it to keep the formatting of the source. The return values in col 2 are 01, 02, 03 etc up to 12 in general text format, but the formula below returns 1,...

Google Sheets Lookup Tool Assistance

google-spreadsheet,vlookup
I've taken it upon myself the learn how to "excel". That made a little more sense in my head, but hey. I have built a "lookup tool" which can be viewed here. The "Dishwasher search" is working as intended. You can search by noise level or decor panel height to...

Excel - SUM of multiple VLOOKUP

excel,table,excel-formula,key,vlookup
It seems like a simple problem, but I do not manage to solve it. I have the following tables: Values | Key | Value | |-----|-------| | A | 1 | | B | 2 | | C | 3 | Results | Foo | Bar | |-----|-----| | A...

Advanced Lookup required

excel,if-statement,sum,max,vlookup
I'm in need of some advanced lookup formula with MAX, MIN, IF and SUM. Info: Sheet1 (data): Column one(A): name Column two (B): Type Column three (D1:AO): Data/numbers Row 1: A1=Name, B1= Type, D1:AO1=name of the different data Sheet2 (output): B5: Primary Critera, Selecting from a dropdownlist of Sheet1.Row(D1:AO1) B7:...

Runtime error 1004. Need to fetch value from another sheet in same Excel file

excel-vba,excel-formula,excel-2010,vlookup,worksheet-function
Sub UpdateFormula() Dim CurrStr As String Dim EndRow As Long On Error GoTo 0 EndRow = Range("A" & Rows.Count).End(xlUp).Row BaseStr = UCase(Range("A2").Value) Application.ScreenUpdating = False For iter = 4332 To EndRow CurrStr = UCase(Range("A" & iter).Value) result = Application.WorksheetFunction.VLookup(CurrStr, Sheets("CustAR").Range("A2:A2499"), 1, False) '=IF(ISERROR(VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)),"NotFound",VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)) Next...

VLookup in VBA is not working however it's working on cells [ SOLVED ]

excel,excel-vba,vlookup
This is my function that to take an array with dates and return the hours per week according to an existing table in my Sheet Eng_Availability_Report. For me, it seems to be correct but VBA sends me a message with Error 1004. I used a MsgBox to return the weeknumber...

VBA Macro Error used with VLOOKUP

excel,vba,excel-vba,vlookup
I recently used code from a post by @LondonRob, which allows the format of a cell to be carried over with the containing data when using VLOOKUP. Origianal question - Vlookup to copy color of a cell - Excel VBA This is great and works for the majority of values....

What could this expression in Excel mean

regex,excel,indexing,match,vlookup
Using INDEX and MATCH for lookups and came across an expression someone used in the form of : =INDEX(*range used*, MATCH(MIN(ABS(data!E2- lookup!$L$5:$L$105)),ABS(data!E2-lookup!$L$5:$L$105),0)) lookup!$L$5:$L$105 is the value lookup table range. I know what its suppose to do but the "data!E2-lookup!$L$5:$L$105" part does not make sense. How does this work? Thanks...

VBA Vlookup a filename

vba,excel-vba,vlookup
I'm trying to include a Dim in a Vlookup. Dim filename As String filename = Format(DateAdd("d", -6, Now()), "mm-dd-yy") filename = "NSC " & filename & ".xlsm" For Each ws In ActiveWorkbook.Worksheets ws.Activate ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-2],'[" & filename & "]" & ws & "'!R8C4:R500C6,3,FALSE)" Next My error comes at...

Excel vLookup Returning 0

excel,vba,excel-vba,vlookup
I'm working with a two very large tables and I'm trying to use vba and vlookups to match the data in one with the other. The worksheets are each one column and are structured like below: Reference Sheet: ID_Ref (2/4) Q1234 W1234 R1234 ... Lookup Sheet ID_Lookup Q1234 P1234 X1234...

Seaching a list of strings againist a list of words and returning a value (Excel)

string,excel,vlookup
In excel I have two sheets. In Sheet1 Column A, I have strings. In Sheet2 I have two columns, Column A contains one word that should potentially be somewhere in a string from Sheet1, and Column B has a return value that I would want if Sheet2!A:A was found anywhere...

Need to fill a sheet with consecutive rows data based on value in another worksheet

excel,vba,excel-vba,vlookup
OK here's the situation. I've created a workbook with 5 sheets in it. The first sheet has columns for QTY, DESCRIPTION, PRICE and some other data, but those are the pertinent ones. Based on the data entered, a labor invoice and several order forms are filled out. That is working...

Lookup in another, dynamically generated, spreadsheet

excel,indexing,excel-formula,match,vlookup
I have a spreadsheet that I need to pull information from another spreadsheet that is automatically generated. The problem with the automatically generated spreadsheet is that the information that I need to pull is not always the same, however the columns that contain the information that I need will always...

VLOOKUP together with MATCH

excel,match,vlookup
I have the below lookup condition but can't seem to get it working. =VLOOKUP(A1,'Raw Data'!A1:A3,MATCH('Submitted Data'!B1,'Submitted Data'!A1:B1)) I'm trying to pull in the B column value from Submitted Data into the B column in Raw Data based of a match on A columns, I just keep getting an error. Raw...

Finding a specific cell contanining value greater than the desired value

excel,indexing,excel-formula,vlookup
I'm looking for a way to return a specific cell value from a range based on an initial desired value. It looks like this: DesiredValue typed in cell: | 125| Search Range: line1 RangeA|RangeB line2 1 | 50 line3 51 | 100 line4 101 | 500 line5 501 | 1500...