CODEWITHSUNDEEP
excelexcel-2007
Punith Gubbi
Punith Gubbi

Reputation: 692

how to find the Specified number lies between list of numbers?

I am trying to find the value of a number in the big list. Below is the sample Input and Desired output Snap Shot.
Please help me out how to do this.

The list is big hence Nested if's will not help, tried with Match & vlookup also but not getting the Desired output.

enter image description here

Upvotes: 0

Views: 1794

Answers (2)

Peter Albert
Peter Albert

Reputation: 17475

Try this formula in E3: 

=IF(D3>$A$6,"No match!",INDEX($B$2:$B$6,IFERROR(MATCH(D3,$A$2:$A$6)+1,1))

Upvotes: 2

itsols
itsols

Reputation: 5582

Based on the sample data you've given, here's the correct procedure:

  1. First sort the values in the list. Sos select the whole range and sort by column A.

  2. Starting at E3, type the following: =VLOOKUP(D3, $A$2..$B$6, 2)

  3. Copy cell E3 to cells down for the newer values.

Explanation: I've used the $ sign before and after the cells that indicate the lookup table. This is to make them absolute. You must do this. Otherwise, the range will change relatively when you copy the forumula.

On some spreadsheet versions the range is indicated by a colon (:) rather than a pair of dots.

I did not test this but this should work.

EDIT: I just noticed your test entries and I think the results aren't going to be produced correctly because my function should give an 'E' for values 100 and above. But you seem to need it for values < 100. So you'll have to re-write the table like this: 0 E 100 D 200 C 300 B 400 A

I'm assuming these are mark ranges and this method would not really check for the upper limit of 500. But it should produce your results correctly.

Upvotes: 0

Related Questions