Vlookup or similar

[bojan0810]

Hi all...

I need formula for this.

example...

A B C D
100 1 180
200 2 120
300 3 200
400 4 455
500 5 499

This is just an example, numbers in my sheets are different in column A not like that 100,200 etc, same as column b. And my data is on other column but that isnt important, I can change formula as I needed.

I tried with nested IFs but max of nested IFs is 64 so I cant use that.

Anyway what I need is this

Prob. a Vlookup formula but not sure. Anyway.

if on column C is 180, column D should be 2. If it is 120 then 2.

So it D column looks like this

D
2
2
3
5
5

Something like Vlookup to find that number from list. I have list of 10000 rows, thats why I need that. With ifs I can only do 64 rows.

Thanks in advance

 

[bojan0810]

One more thing. If it is easier for vlookup I can move column B whole 10000 rows to 1 up if needed.

 

[h2so4]

(e-mail address removed) explained :

One more thing. If it is easier for vlookup I can move column B whole 10000
rows to 1 up if needed.

hello,

you could use the following formula

=INDEX($B$2:$B$6,MATCH(C2,$A$2:$A$6,-1))

but your table A B needs to be sorted in descending order

and check why a 200 in column C should return a value 3 in column D(and
not a 2 as I would expect)

 

[bojan0810]

it works like this.

If number is greater and equal to 200 till 300 it should write 3, and so on...

so 200 is 3

300 will be 4
100 will be 2 etc... 99 will be 1, 199 will be 2 etc

 

[Claus Busch]

Hi,

Am Fri, 25 Apr 2014 00:35:23 -0700 (PDT) schrieb (e-mail address removed):

If number is greater and equal to 200 till 300 it should write 3, and so on...

so 200 is 3

300 will be 4
100 will be 2 etc... 99 will be 1, 199 will be 2 etc

in D1 try:
=ROUNDUP((C1+1)/100,0)


Regards
Claus B.

 

[bojan0810]

Dana petak, 25. travnja 2014. 09:45:37 UTC+2, korisnik Claus Busch napisao je:

Hi,



Am Fri, 25 Apr 2014 00:35:23 -0700 (PDT) schrieb (e-mail address removed):







in D1 try:

=ROUNDUP((C1+1)/100,0)





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Hi Claus. Thanks

That would work perfect if values in B column are like that. As I said in post, that is just example, values are way different in column B on my sheet.. I put 1,2,3,4,5 for example, but it can be 2 4 6 8 etc, it changes based on other criteria what isnt important for this. I need formula that it can find like that in post.

 

[bojan0810]

I can move column B up or down if that will help.

For example, just a thought.

If number in column C is less then some number in list in column A, it should write number from Column B that is next to that number in column A.

For example. Number is 250.

That is less then 300 so it should write 3. And number 3 is next to number 300.

 

[bojan0810]

Yeah that you formula does, but what if numbers in column B are different.

But thanks, I will use that formula from you to some other sheet because it is perfect for other one.

Anyway. I figure it out an solution. I moved column b up by one. So it doesnt start as 1,2,3,4,5, it starts with 2... 2,3,4,5,6 etc.

And this formula did the trick =VLOOKUP(C1,$A$1:$C$5,2,TRUE)