Free Microsoft Excel 2013 Quick Reference

Lookup formula returning same value for multiple lookup values

I am using a formula to retrieve information from one worksheet to another.

To simplify my issue, see if you can understand the example below:

1. Open a new worksheet
2. In Column A, enter the numbers 1 thru 9 down the column
3. In Column B, enter the numbers A thru I down the column
4. In Column F, list the following numbers down the column: 5, 9, 4, 12, 6, 7, 22, 3, 450
5. In Column G, enter the formula: =LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)
6. Copy the formula down and look at the results.

For the values 12, 22 and 450, the logic should have failed, but returns the bottom-most value from the return value array.

Basically, I am trying to compare two lists, and when an item does not appear in the first list, it should return a blank value. If the item does appear, then it should return the corresponding value for a cell on the sheet.

Any ideas?

Thanks for you help!

Take care.

RPW


Post your answer or comment

comments powered by Disqus
I am trying to figure out how to write a formula that searches column F for multiple values. Those values will either be Regular, Husky, or Slim and they will be in any random order and with duplications.

Basically, I would like for this formula to search column F and if it finds "Regular", then it returns "100", if it doesn't find "Regular, then it searches for "Husky" and returns "200", and if both "Regular" and "Husky" aren't found, it will search for "Slim" and return "300". If none of these values exist, then it returns "000".

I understand the logic of all this, I just can't seem to figure out the proper function to use to make this work.

Anyone know what to do?

Dear Forum,

I recall seeing a COUNTIFS formula once that utilised the curly parentheses to search for multiple values within a single column but am struggling to repeat the same. I am developing a spreadsheet to lookup multiple values (sample below) within a range and then count how many were completed on a particular date. So far my formula looks like this:

=countifs('Raw Data'!B2:B10000,"Y",'Raw Data'!C2:C10000,D11,'Raw Data'!A2:AC10000={A6:A40})

where "Y" determines if it's complete, cell D11 holds the date on which it was completed and the range in the {} equals the list below.

EXTRACT OF LIST OF VALUES
041-12
075-02
413-01
416-01
418-07
418-09
437-01
470-02
655-06
............etc.

I've attached a sample workbook for clarity.

Hi

Could you please help out with a formula needed to find a value for Multiple ranges. The data given below needs to be found

Name Name1 Name2 Name3
Date Number Date Number Date Number Date Number
1/2/2007 8 1/2/2007 8 1/2/2007 8 1/2/2007 8
1/3/2007 9 1/3/2007 9 1/3/2007 9 1/3/2007 9
1/4/2007 8 1/4/2007 8 1/4/2007 8 1/4/2007 8
1/5/2007 7 1/5/2007 7 1/5/2007 7 1/5/2007 7

The Result required is :

Name 1/2/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/3/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/4/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/5/2007 (Number required by using Name as first look up, Then
Date as second look up)

Name1 1/2/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/3/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/4/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/5/2007 (Number required by using Name as first look up, Then
Date as second look up)

Name2 1/2/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/3/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/4/2007 (Number required by using Name as first look up, Then
Date as second look up)
1/5/2007 (Number required by using Name as first look up, Then
Date as second look up)

Is there any formula that can be used to look up name first and then using the date cell below that name to find the number ?

I see how to set conditional formatting for one name, how do I set it for multiple values?

Column A contains one of three names: Teri, Linda, Evelyn (Each name is repeated, randomly, through the column)
Column B is a variety of Categorys (Some text, some numeric, all different)

Cell A2 = Teri, I want cells A2 and B2 to be Orange, Accent 6, Darker 50% Font and Orange, Accent 6, Lighter 80% Fill

A3 = Linda, I want cells A3 and B3 to be Dark Blue, Text 2, Darker 25% Font and Aqua, Accent 5, Lighter 80% Fill

A5 = Evelyn, I want cells A5 and B5 to be Olive Green, Accent 3, Darker 50% Font and Olive Green, Accent 3, Lighter 80% Fill

I would like to format both columns so that as we add entries, both columns will color code according to the name in column A.

Hi All,

Can we apply two formulas in same cell for data validation ?

As per my requirement, I have tow column say 1,2 and both column contains list data validation.

I want column 2's value to be changed based on column 1.
kinldy let me know how I can solve my problem.

Regards,
Amit Dewangan

Hiiiii... to All.

How do make excel to consider single value in multiple values in a particular cell.

For example if in a cell values are " 358, 58787, 6865".

Excel will take them as a single value, and I want it to take it as a multiple value. After and before comma value should be treated as a single value. also wants to define a delimiter in a cell ( In above case it would be ",").

Also How do I sort them.

How do I add (=Sum) the same cell for multiple sheets (103). I don't want to
use the +sum(sheet1,sheet2,etc) 103 times.

Has anyone else had a problem with the lookup formula returning an incorrect
result? I have found that if there is no match, it will return the result
from the previous line. Does anyone have a solution?

Hi, I hope someone out there can help me with this as I have spent 2 days trying to work it out and am getting nowhere!

I need a formula that will do as a VLOOKUP does with returning a lookup value, but I need it for multiple values and VLOOKUP will only return the first value.

I though I had got somewhere with an INDEX formula but this is not working, I've attached a sample file - if anyone could help me with this it would be much appreciated.

Thanks

Nikki

Hi

I have a query with excel functions. Using vlookup to search for a value and display the result in a particular column of a certain sheet as per formula below:

= VLOOKUP (F3 PAGES!A2:E6. 3. FALSE)

where:
F3 = the reference value to check for
Pages!A2:E6 = The worksheet to check from
3 = The column number of the result to display

Is it possible to search for more than 1 value? 1 of which is a number, the other is text?

I have attached an example spreadsheet. I would like to search for values in the first to column and return values for 1 of the remaining columns. So, the ID for London in the sheet remains the same, but I need to match the specific London site i.e. London A or London B in order to display the correct returned value. In this case I am hoping to match the ID for London and specifically London B, hence I would like to display results from column 3 in the row of London B. How may I represent this in a formula?

kind regards

buzf355

Hello there!

I am using this formula
to lookup the values for the empty column I on sheet 1, which are present on sheet 2 although in a different order and
sometimes even non-existant.

Here is the problem:
I have, for testing purpose, a file with 2 identical sheets, only difference is that I deleted all numbers from column I on sheet 1, but all the values are present in column I on sheet 2.
Each value in column is categorized by a unique number in column E, thus this is the way to find out the correct value for the listed unique number.
Now, when I insert this formula in sheet 1 column I, It looks through sheet 2 and gets me to correct the value for each unique number. And here is the kicker: the total SUM of that generated number is different than from the original (and 100% correct) total I have on sheet 2.

This formula works fine on my dummy sheet with 10 rows, but it does not in my actual workbook with 2000 rows...

Help please!
Thx, A2k

I need to find a way to match two items and then do a lookup in the same row but different column. My workbook has two sheets, one called report and the other data. The data sheet looks as follows:

Column A Column B Column C
Column D
Group # Employee #  Employee Name  Sales
  1       10             Mike        $40
  1       15             John        $30
  1       23             Bill        $20
  1       34             Ted         $13

  5        5             Frank        $5
  5       15             John        $22
  5       31             Ray         $53
My report sheet looks as follows:

Column A Column B Column C Column D
Group # Employee #  Employee Name  Sales
  1       10             Mike        
  1       15             John        
  1       23             Bill        
  1       34             Ted         

  5        5             Frank       
  5       15             John      
  5       31             Ray

What I'm trying to do is create a formula in Column D of the report sheet that will lookup the dollar value that is in column D of the data sheet and display it. At first I was just going to use a vlookup to find each persons employee # but then I discovered that the same person can be listed in more then one group. How can I setup a formula that will match both the group # and employee # and then look in the column D of the same row for a value? Thanks in advance for any help

Hi, Please help! I have a bunch of pretty horrendous spreadsheets given to me with colour formatting and enormously inconsistent data, but for Industrial relations reasons I can't change the layout at all.

What I need to be able to do is to find values of all cells in {Roster!$D$2:$D$19} with a given value and copypaste relative cells from {Roster!$A$2:$A$19} into another sheet.

I presume I will need a bit of VBA and to create a LOOP but I know that LOOPs are perhaps a little slow but I need the script to do something like this:

=INDEX(Roster!$A$2:$A$19,MATCH("0415-1230",Roster!$D$2:$D$19,0))

Obviously this will only return 1 match for the 1st instance of "0415-1230" but I need it to repeat so that it finds all occurences of "0415-1230" in the column and copy them all to sheet called "Summary"

Then search the same column (Roster!$D$2:$D$19) again for all occurrences of "1145-2045" and paste into another column in Summary!. Then the same again for "0615-1515" and so on for about 5 vaues in total.

Heres an example of the data I am using (column D):

Roster.jpg

And how I need the results to look:

Summary.JPG

Only the specified values (0415-1230 etc) will be counted. Any zeros or instances of "Holiday" or "Sick" will not be
counted.

Hope this is clear, thanks in advance, Mark

I reviewed the threads to see if this question has been answered, I couldn't find the solution. Sorry if it's a repeat...

I need assistance with finding and replacing multiple values in two columns in excel. I am choosing find and replace, but if there is another formula that would be more useful, please let me know.

I have two colums in excel, I need to search for data in Column A and replace with the data from Column B.

For example:
Column A
Atlanta
Concord
Nashville
Memphis

Column B
123- Concord
768-Memphis
123-Nashville
333-Atlanta

I need to find the values in Column A and replace them with their match from Column B. I have 16,000+ values to perform this process on.

Thanks,
LM

I have a worksheet with 4 columns with IF formulas with column headings ABC
and D, then a column with a LOOKUP formula refering to a row vector spanning
A to D. The LOOKUP formula loos for a certain value on fields ABC and D, and
has to return the column heading. The LOOKUP formula is evaluating correctly
for fields BC and D, but for field A, it returns the heading of column D.
Does someone have any advice on this? I have tried the same setup on
different files, and I get the same problem.

I am trying to link data from one worksheet to another using the IF formula.
The first worksheet (roster) includes student information, status (active or
inactive) and assessment units completed (designated as Y or N for each
unit). The subsequent worsheets include the data for each unit.

With the help of Max--I used the following formula in the second worksheet
to return a value only if the student was active and had completed the unit:

=IF(Roster!$A9="I","",IF(AND(Roster!$A9="A",Roster !$H9="Y"),Roster!B9,""))

However, if a student is inactive (I) --a blank cell is returned. Is there
any way for the values of only the active students (A) to appear on the
worksheet sequentialy (without the blank cells)?

Your help is greatly appreciated!

Hi All,

I am looking for a Formula that can process the following:

The Sum Target Value is variable e.g.; 147
I have two columns of numerical values: Column “A” and Column “B.”
Column “A” Houses the Numerical Labels that I wish to have Returned when the
Sum Target Value is processed /reached - Subtract Sum Target Value as noted
below.

To Subtract Sum Target Value:
Start from LAST non-zero numerical value in Column “B” and Subtract one Cell
value at a time (or Sum up the Column) until the Sum Target Value or nearest
possible Sum BELOW, the Sum Target Value is reached. In this instance, it is
147. I wish to Sum the values in Column “B” to 147 per the above. The
summed values can be below BUT NOT over the Sum Target Value.

Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”)
and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target
Value. The Result – Numerical Label should come from Column “A.”

Col “A” Col “B”
200 3
205 1
210 5
215 11
220 10
225 15
230 16
235 10
240 11
245 29
250 20
255 27
260 22
265 34
270 24
275 36
280 30
285 25
290 31
295 26
300 15
305 18
310 23
315 17
320 9
325 11
330 2
335 9
340 1
0 0

Reaching the Sum Target Value of 147 in Column “B” would go up to value 26,
Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but
including the value of 31 above it, would exceed the Sum Target Value of 147.
The required Result is returned from Column “A” Label 290 which is ONE Cell
to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column
“B”).

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

Hi Guys,

I have a formula that counts the number of cells in a range that do not have a certain value in.

=(COUNTIF(C5:I5,"<>A"))

I need to expand this so that the formula gives a count for all cells within the range that either:

DO NOT have multiple values "A", "S", "T", or "H"
or
DO have values "Y" or "N"

Can anybody point me in the right direction.

Thanks in advance.
Mark

Hi All,

I am looking for a Formula that can process the following:

The Sum Target Value is variable e.g.; 147
I have two columns of numerical values: Column “A” and Column “B.”
Column “A” Houses the Numerical Labels that I wish to have Returned when the
Sum Target Value is processed /reached - Subtract Sum Target Value as noted
below.

To Subtract Sum Target Value:
Start from LAST non-zero numerical value in Column “B” and Subtract one Cell
value at a time (or Sum up the Column) until the Sum Target Value or nearest
possible Sum BELOW, the Sum Target Value is reached. In this instance, it is
147. I wish to Sum the values in Column “B” to 147 per the above. The
summed values can be below BUT NOT over the Sum Target Value.

Return the Numerical Value that is Offset ONE Cell to the LEFT (Column “A”)
and ONE Row Above LAST value Subtracted (in Column “B”) to reach Sum Target
Value. The Result – Numerical Label should come from Column “A.”

Col “A” Col “B”
200 3
205 1
210 5
215 11
220 10
225 15
230 16
235 10
240 11
245 29
250 20
255 27
260 22
265 34
270 24
275 36
280 30
285 25
290 31
295 26
300 15
305 18
310 23
315 17
320 9
325 11
330 2
335 9
340 1
0 0

Reaching the Sum Target Value of 147 in Column “B” would go up to value 26,
Label 295 in Column “A”, totalling 131 which is below the Sum Target 147 but
including the value of 31 above it, would exceed the Sum Target Value of 147.
The required Result is returned from Column “A” Label 290 which is ONE Cell
to the LEFT (Column “A”) and ONE Row Above LAST value Subtracted (in Column
“B”).

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

I would like to use the same range for two different functions such as AVERAGE and STDEV. For small data sets or a single sheet this is not a problem, as I can just input the same range manually. However, I have a lot of data that I am working with and would like to only input the range once and have the second cell reference the same range. So for example, if I have three values in A1, B1, and C1 I would like to put an AVERAGE formula in D1 to average A1:C1, and then have a STDEV formula in E1 that would also automatically reference A1:C1.
I thought about named ranges, but again, I'm dealing with a lot of data and do not want to have to name hundreds of separate ranges.

Sample file attached.

Hello, I have two worksheets I have to match and sum data from. In the first work sheet I have a phrase I need to lookup in the second sheet from a list of website addresses, but there will be multiple matches in this address list and I need to sum the corresponding values. For example: sheet one cell A1 is ATM_12 and in sheet two E2:E6576 have web addresses like http://abc.com/index.php?source=google&landing=atm_12 and corresponding values in N2:N6576

I have figured out how to sum on a exact text search using: =SUMIF(Sheet3!E2:E6576,"http://www.abc.com/index.php?source=google&landing=ATM_12",Sheet3!N2:N6576) but since there is a list of about 50 phrases and the web address domain varies, I cannot effectively use an exact search like this.

Anyone have any suggestions?

Thanks in advance-

Ryan

I'm trying to return a sumif based on multiple values.

I've attached a spreadsheet of sample data, to help explain:

I need to be able to return in a single cell a sum of values where eg: product = b, Region = All, cost type = h & the date is less than October 08.

This should give a result of 430.

These will be budget figures, so I want to be able to pull out the cumulative budget for particular lines each month.

I've tried a concatenated Index/Match function, but that won't return the sum as it seems to be looking for a single cell.

Any ideas?

Thanks

I have 2 lists. List A is just a set of strings. List B is a set of the same strings with multiple values associated to them. For List A, how do I return the minimum value for the same string in List B?

Example:

List A
string1
string2
string3

List B
string1 3
string1 6
string1 2
string2 10
string2 5
string2 2
string3 4
string3 5

If anyone knows an easy way to do this or has an example of some code, I'd be stoked if you could share it!

I'm basically trying to extract a date corresponding to a maximum value in a series - I know I can use the VLOOKUP worksheet function for this. However, my quandry is that I need to do this for multiple values in the same series (for example: maximum occurs on 3 occasions). Is there some way that I can do it easily, or does it need code (I do code, just not fast, well or with much flair).

Cammo


No luck finding an answer? You could always try Google.