Vlookup or similar

B

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
 
B

bojan0810

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

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)
 
B

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
 
C

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.
 
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.
 
B

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.
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top