**10**

votes

**3**answers

99k views

### Quick replace #N/A by 0 when vlookup

I use vlookup a lot in excel.
The problem is with #N/A value when the seek value is not found.
In that case, we often replace it by 0 using
if(isna(vlookup(what,range,column,false));0; ...

**0**

votes

**2**answers

28 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 ...

**0**

votes

**0**answers

18 views

### Extracting a Sublist from Master List

How do I write a formula to find the exact matched of the subsets and extract it from the master?
Basically what I have is a master data spreadsheet of 275,000 rows of data. I then have a queried ...

**0**

votes

**2**answers

306 views

### vlookup with blank look up value

I have a Vlookup formula that I want to return 0 if the returned value is empty.
example:
=(VLOOKUP(A6,F:G,2,0)+1)*C6
+-------+--------+-------+-----------+-------+-------+
| | | ...

**0**

votes

**1**answer

20 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, ...

**0**

votes

**3**answers

751 views

### I need the number in a cell to countdown when data is entered into other cells.

I'm making a sheet to keep track of the number of software licenses.
In cell B1 I have the number 35 representing 35 licenses. As they are assigned to systems in cells C5-C40 I would like the total ...

**0**

votes

**1**answer

125 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 ...

**1**

vote

**1**answer

37 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 ...

**0**

votes

**2**answers

36 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 ...

**6**

votes

**5**answers

9k 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**

vote

**1**answer

29 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, ...

**0**

votes

**2**answers

2k views

### How to return all the cells in a row if first column matches a value on another worksheet

I have 2 worksheet tabs in an Excel workbook, tickets and tickets info. I have values in Column A of tickets (from A2:A500). I want to search for each value from A2 to A500 of the ticket sheet looking ...

**1**

vote

**2**answers

34 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 ...

**0**

votes

**2**answers

101 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
...

**0**

votes

**1**answer

34 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) ...

**0**

votes

**2**answers

32 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 ...

**0**

votes

**0**answers

38 views

### How to keep a Value in Excel (constant value)

I have set up a formula,
=IF(T74="1/2Dry Stack",0.1,IF(T74="DryMortar Joint",-0.1,0))
Linked to a drop down list on another sheet. How can I keep the value of the formula after making another ...

**0**

votes

**1**answer

35 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 ...

**0**

votes

**0**answers

22 views

### How do I IF & VLookup?

How do I ask IF the cell is found in another table pick the cell information next to it in the other table?
I know this is an IF and Vlookup formula, but I'm not getting the If statement correct ...

**0**

votes

**1**answer

123 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

346 views

### When switching to another doc, Function Argument window disappears?

I have problem with Excel 2007 when using VLOOKUP function. After switching to another document for referencing value in "Table_array" field, window dissapears.
Google finds some results about the ...

**0**

votes

**1**answer

26 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)
...

**1**

vote

**1**answer

153 views

### Excel Sort and Lookup Values Formula

I am trying to create some sort formulas on excel.
An example is shown below regarding players and their corresponding scores. I need to query the "Raw Data" and get the "Top 3" players and their ...

**0**

votes

**2**answers

846 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

**0**answers

40 views

### Vlookup stopped working when I changed the lookup cell reference

I have a case where I need to change the lookup value reference in a Vlookup but I can't get the new formula to work.
Old Formula:
=IF(IFERROR(VLOOKUP($CQ2;'TrackerStatus ...

**0**

votes

**0**answers

49 views

### Conditional formatting to highlight differences between 2 pivot tables in Excel

I have an Excel issue I hope somebody may be able to help me with please?
I have 2 pivot tables that both contain the following fields:
'Provider ID' in the Rows area,
'Month' in the Columns area ...

**0**

votes

**1**answer

60 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 ...

**0**

votes

**1**answer

1k 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, ...

**2**

votes

**1**answer

54 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 ...

**0**

votes

**1**answer

49 views

### finding two adjacent cells using vlookup()

In preparing my data for an interpolation within an interval, I am using the MS Excel vlookup() function to find a pair of values that will define the interval for my interpolation.
The default ...

**1**

vote

**1**answer

7k views

### Excel IF and VLOOKUP function help

The exercise is to use IF() and VLOOKUP() functions to return the tax rate based on the annual income and number of people in the household.
So far I've tried inputting:
...

**-4**

votes

**1**answer

97k 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?

**0**

votes

**1**answer

37 views

### Need to transfer a column's data. I have 2 sheets with same data and need to move corresponding data from one to another

I have 2 excel sheets and both have one column that has matching data. However Sheet2 has a column of data that Sheet1 does not have and I need to input the corresponding data from Sheet2 into Sheet1 ...

**0**

votes

**1**answer

31 views

### VLookup function solution requested

I have an excel spreadsheet which has got all the data. Column A has the staff initials such as AA, SH, SG.This goes down the column. All other columns there are various data such as fees, amount ...

**0**

votes

**1**answer

36 views

### how to concatenate the output of vlookups in one step

I'm trying to run two vlookup operations and concatenate the results in one formula as such:
=CONCATENATE(
VLOOKUP($A$6,'TRP'!$A$7:$D$655,2,FALSE),
"/",
VLOOKUP($A$6,'HCP'!A5:B655,2,FALSE)
)
The ...

**0**

votes

**1**answer

68 views

### Return value with if and vlookup

I'm trying to create a formula that can help me maintain a list.
I have drawing numbers and revisions on one sheet. I need to like bring that to the main sheet for each drawing number that is on the ...

**0**

votes

**1**answer

66 views

### Reverse vlookup

I have a list of descriptions and I want to add a code column as shown below. The descriptions in the list are neither unique nor sorted.
I have an index table with substrings and the codes in ...

**1**

vote

**2**answers

4k views

### Excel Vlookup for 2nd, 3rd, 4th or 5th text value, instead of 1st matching value

I have rows of possible duplicate employee ID #'s, with another column of different values for the same person. I need a single column of unique ID's with the certifications transposed in the same ...

**0**

votes

**1**answer

241 views

### Build a lookup query in Excel using named ranges stored in a cell

I have a spreadsheet which has a lot of named ranges, each of which is a table. I want to be able to run a lookup that will retrieve a value from any of the tables. I want to be able to store the name ...

**0**

votes

**1**answer

107 views

### VLOOKUP cells that contain a certain text in Excel

The challenge is as follows: I have a column with names extracted from a source. The names come in different ways each time it is extracted. For me to be able to use it, I need the names in a specific ...

**2**

votes

**2**answers

212 views

### Excel - return multiple values for each of a list of values

I've been trying to solve this for a good few hours now, but with no result...
My setup:
I've got excel sheets of cosmetics product recipes.
Each recipe contains dozens of ingredients, each given ...

**4**

votes

**4**answers

89k views

### Excel - How to vlookup to return multiple values?

I'm looking to use Excel to look up and return multiple reference values for a given key. VLookup does something very similar to what I need - but only returns a single match.
I assume it'll involve ...

**3**

votes

**2**answers

676 views

### Excel, how to use text functions on lookup array without putting in another column?

If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column?
Let's say I ...

**2**

votes

**1**answer

21 views

### Making one list from other data using a lookup

I have 2 columns of data in an excel sheet - Column A:A is an id number, and column B:B is a binary 1/0 response. I have > 1, 000 rows of data.
Example
0001 1
0002 0
0003 1
0004 ...

**0**

votes

**0**answers

37 views

### Find and Replace in .txt file by extracting data from spreadsheet

I need to replace instances like Manhole Name="1" with Manhole Name="SW3/001" for instance by extracting the data from spreadsheet.
Would that be possible using regex, or in any possible way ?
...

**1**

vote

**2**answers

50k 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

**2**answers

779 views

### How to lookup a value and return multiple unique values to a single cell - comma separated

I am attempting to lookup a unique identifier (Product Number) which is held in several different stock locations. For example:
__A__ __B__ __C__
1 Item # Warehouse ...

**5**

votes

**2**answers

64k 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

**1**answer

42 views

### VLOOKUP returning N/A because references contain “.” and “ ” in one table, not in other

OK
I am trying to vlookup a table but the problem is the references on one table are slightly different to the references on the other table. I can get the VLOOKUP function working, but it is ...

**0**

votes

**1**answer

140 views

### How do I set a dropdown in excel to populate different data in cells below based upon the option selected from the dropdown?

I am attempting to clean up a spreadsheet that we have that tells us what permissions to grant for certain roles in our ticketing system.
I have used the Data Validation tool to create my dropdown ...