Ask Your Question
0

Simple add to col X if col Y is that?

asked 2017-05-09 18:24:56 +0200

krisuvirt gravatar image

updated 2017-05-09 18:48:36 +0200

Hi,

sorry if this has asked million times earlier, but i dont have a clue what kind of tags or keywords i should search.

I need a function, which removes 1 from certain row after certain value has given.

For example, C3 is input. If given value 532, then function searches from col 1 a row which has same value as input. Then it removes 1 from quantity, which is at col 2 in same row.

Im sure i can avoid negative problems and such, if i just get some directions how to start with this one.

Thanks, Krisu

edit retag flag offensive close merge delete

Comments

Hello krisuvirt, to compute the new quantity you could write :

=INDIRECT(CONCATENATE("B"; MATCH(C3;A1:A99999;0)) ;1) -1

i think it needs a macro to enter this new value into the cell in Column B..

librebel gravatar imagelibrebel ( 2017-05-09 20:26:42 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2017-05-09 20:00:23 +0200

Lupp gravatar image

updated 2017-05-09 20:02:09 +0200

Searches (MATCH(), VLOOKUP(), ...) have to get the value to search for and a range to search in. In case of VLOOKUP() this range is extended (at least) as far as needed to alo contain the column where the associated value to return is located. Read the help about the related functions. If you want to search 100 rows starting with number 2, the following formula should do:
=VLOOKUP(C3;$A$2:$A101;2;0)-1
If the value to return is not contained in the same row as the match, you can use a combination of MATCH() with INDEX() or OFFSET().

edit flag offensive delete link more
0

answered 2017-05-10 18:41:39 +0200

krisuvirt gravatar image

Ok, thanks folks. Ill try with these.

  • Krisu
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-09 18:24:56 +0200

Seen: 34 times

Last updated: May 10 '17