E-Mail 'How to return a value if lookup value is in a range' To A Friend
Email a copy of 'How to return a value if lookup value is in a range' to a friend
Loading ... Loading ...30 Responses to “How to return a value if lookup value is in a range”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Great post, Oscar. There are simpler formulas to do this, though.
=VLOOKUP($D$8,$B$4:$D$6,3,TRUE)
or
=INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))
will also return B.
Thumbs up!!
I guess the columns must be sorted ascending in order for the formulas to work properly.
Thanks for your contribution!
[...] Return value if in range in excel, Oscar shows us a formula for returning values in a column based on a number range. Let's review [...]
happened upon this while calulating rating scores for timed surgical evaluation... needed formula to output score based upon time ranges... "formula in cell D10" worked perfectly! Thanks SO much!!!!
~charmain~,
I am happy you found it useful!
I am looking at a way to create a Bring Forward system witht the dates of the calendar.
It would be the first wednesday of the year like 2012-01-04 to 2012-01-18 would return the value of 1, and 2012-01-19 to 2012-02-01 would return the value of 2, etc...
Hi Oscar,
I came across this amazing formula when i was trying get a value return in a range:
INDEX(D4:D6, SUMPRODUCT(--($D$8=B4:B6), ROW(A1:A3))) + ENTER
But could not understand the usage of "--" in the sumproduct and also the logic behind using sumproduct itself.
Could you please help me to understand
Logical function (i.e. =46) you will always get numeric value 0. You can not convert FALSE to anything else other than numeric value 0.
It is not common to use more than two dashes since it can mess up your TRUE result (i.e. =---------------(TRUE) will return numeric value -1)
Logical function (i.e. =4 is less than 6) will return TRUE or FALSE value.
If you add one dash (-) in front of this logical function the result is the opposite (negative) value in numeric form; in this case result is -1
So if you add two dashes (--) in front of this logical function - the result is the opposite of the opposite (negative of the negative) in numeric form; in this case result is 1
The whole point is to easily convert result TRUE to numeric value 1, or convert result FALSE to numeric value 0.
p.s. of course, if you add as many dashes in front of logical function which results FALSE (i.e. =4 is more than 6) you will always get numeric value 0. You can not convert FALSE to anything else other than numeric value 0.
It is not common to use more than two dashes since it can mess up your TRUE result (i.e. =---------------(TRUE) will return numeric value -1)
Brilliant solution
I'm using the first formulation, but it assumes that the value fits between exactly 1 of the ranges. If it fits none, it returns the same value (which is not a big problem). If it fits on several ranges, it returns an error.
How do get it to provide at least one of the possible ranges instead of an error when there are several possible ranges?
Thanks!
Karl,
Array formula:
Attached file:
Return-value-if-in-range-karl.xls
Good day sir.
=INDEX(Calculate!D2:D6,SUMPRODUCT(--(Calculate!$D$9=B2:B6),ROW(Calculate!A1:A5)))
As you can see, I'm trying to put the formula to another sheet. Unfortunately I'm getting #value error.
Please advice, thank you.
Maybe oversimplifying it but wouldn't this solve your vlookup problem?
https://www.excelvlookuphelp.com/how-do-i-use-a-vlookup-where-the-range_lookup-is-true-rather-than-looking-for-an-exact-value-looking-for-a-range/
I have been searching for such formula...
May i know hot to do if value columns are more than one like Value-I, Value-II,Value-III and inputs are number and value type
Data is Range-I|Range-II|Value-I|Value-II|Value-III
Inputs are Number|Value-I
A formula which matches range of numberical input and match value type and copies the intersecting value...
To be simple two way lookup.
Hi
What if I want to use the same formula, but to change the range of the two columns B and C to be date range (07/01/2006 , 06/30/2007), and to change the value of column D to be a percentage (%450)
How can I use this formula please?
Alternatively this formula can also be applied:-
=LOOKUP(2,1/(($A$1:$A$3=F2)),$C$1:$C$3)
The first '2' represents the number of column to be searched for.
the expression '1/' represents the column number in the array.
'*' represents AND command.
The command will check the condition of whether F2 is greater than or equal to the values mentioned in the column 1. and then checks the condition that F2 is less than the value mentioned in the column 2 in the same row. Then displays the value corresponding to the same row in the column C.
Thank you all
It worked
I mean this: "=VLOOKUP($D$8,$B$4:$D$6,3,TRUE)"
I think it was the best solution
Hi All
I need some help to add rows to a spreadsheet based on the input of the user.
I ask the user to input the number of rows they need to add by inputting the value into a cell k6. Ideally I'd like them to press a nacro button which would the add the specified number of rows below row 8, keeping the same formulas in E8, G8, H8 and I8.
Can anyone assist?
ian
Hi Ian,
I made a workbook for you:
https://www.get-digital-help.com/wp-content/uploads/2010/01/Ian-inset-rows.xlsm
I continue to get #NAME? result using the first formula
My formula is:
=INDEX(F77:F80, SUMPRODUCT(--($D$83=D77:D80), ROW(A1:A4))) + ENTER
$D$83 is the Weight of a product, in numerical format.
Where F77:F80 is a list of prices (based on weight breaks)
D77:D80 is the min weight in a weight range
E77:E80 is the max weight in a weight range
As example of what would be in D77:F80
200,000 | 299,999 | 14.05
300,000 | 349,999 | 13.10
400,000 | 449,999 | 15.60
350,000 | 399,999 | 13.50
IF D83 is 330,327.
The result should be 13.10
Any advice?
I'm trying to create a formula in 4 different cells ”G3 – G6”, to search a column “B3 – B32” (ie: 4/1/17 – 4/30/17) filled w/dates and add a value from column “C3 – C32” if the date falls between the specified date range (ie: cell G3 reads column "B" for dates between 4/1-4/7, cell G4 reads column "B" for dates between 4/8-4/14, cell G5 reads column "B" for dates between 4/15-4/21 & cell G6 reads column "B" for dates between 4/22-4/30).
Hi. Can you please help me. I already have the IF formula where i want to generate a specific value from a column if between two dates.
Example:
Column A1 Start Date, Column B1 End Date, Column C1 Headcount
Data in 2nd row: Start date Jan. 1, 2014 up to Jan.31, 2015, headcount of 7.
FOrmula in Column D2:
=IF(AND(D1>=A2, D1 <= B2, C2, "")
The formula is working fine but the problem is when there is blank or no date in End date column, no value is returned. I would like to return the value in C2 whether there is end date or not. I would like to make use of one formula only that will work whether the End date has data or not.
Your help is very much appreciated. Thanks
JD,
Try this formula:
=IF((D1>=A2)*(D1<=B2)+(D1=""), C2, "")
i have query regarding returning multiple value against multiple checking and where i found value yes i have to return header row value by concatenate . example value
Mismatch - Recipient GSTIN Mismatch - GSTIN of the Supplier Mismatch - Invoice/Debit Note/ Credit Note (No) Mismatch - Invoice/Debit Note/ Credit Note (Date) Mismatch - Original Invoice No Mismatch - Original Invoice Date Mismatch - POS Mismatch - Supply attract reverse charge Mismatch - Total GST Rate Mismatch - Taxable Value Mismatch - IGST (Amt) Mismatch - CGST (Amt) Mismatch - SGST/UTGST (Amt) Mismatch - Cess(Amount)
No No No No No Yes Yes No No No Yes Yes Yes No
No No No yes No Yes Yes No No No Yes Yes Yes No
Hello Oscar,
I hope you are healthy and well in these troubled times.
re: https://www.get-digital-help.com/return-multiple-values-if-in-range-in-excel/
This doesn't work in Excel 2013 (or whichever one uses "|" instead of "," to separate arguments). Can you give me an update on what an updated version of your formula would be? Thank you very much!
great , this article is and excel file was the exact help i was looking for thanks for this brilliant article
Hi Oscar
I have two columns of numbers one is lower and one is higher. I have a third column with single numbers. I want to see if the third number is within the range of the two columns. If it is I just want it to say true. But I want check all of the ranges not just the ones across from them.
Any help would be very appreciated.
I have a range of data in 3 columns (B2:G6) and I want to find a value within B2:D6 and return a value that is exactly 4 cells to the right. How can I accomplish that?
Thanks in advance!