Excel formula to get certain cell if the value is between 2 numbers

0 votes

On the left, there is the table I have, on the right the desired output

Table

I tried the following function but only works if the values in D and E are in the same column (E) one under the other

=IF(AND(G1>=$E$3:$E$10;G1<=$E$3:$E$10);"No match!";INDEX($D$3:$D$10;IF.ERROR(MATCH(G1;$E$3:$E$10);1)))
Feb 11, 2023 in Others by narikkadan
• 63,720 points
868 views

1 answer to this question.

0 votes

So, first with vlookup():

enter image description here

A formula so you can copy & paste:

VLOOKUP(D2,$A$1:$B$5,2,1)

For those who think vlookup() is a poor choice, here is the use of index() with match(), which needs more typing and no benefit in this situation:

INDEX($B$1:$B$5,MATCH(D2,$A$1:$A$5,1))
answered Feb 11, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,720 points
355 views
0 votes
0 answers

What is the formula to keep first two words in a cell over excel?

I want to maintain the first two ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,010 points
255 views
0 votes
1 answer

Excel formula to get cell color

Add a Name(any valid name) in Excel's ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,720 points
9,444 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,398 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,672 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
816 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,975 views
0 votes
1 answer

Check multiple cells in excel, move to next cell if null and if value found stop and return that value

Use this formula: =INDEX(FILTER(A2:D2,A2:D2<>""),1,1) INDEX returns the first value ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
381 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP