Simple add to col X if col Y is that?

[krisuvirt]

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

[librebel]

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…

[Lupp]

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().

[krisuvirt]

Ok, thanks folks.
Ill try with these.

• Krisu