Get cell value on the left Excel with VLOOKUP

Question

Get cell value on the left Excel with VLOOKUP

Navigation

#1 by (0 votes)

0

I have a sheet with several sheets, where when writing a text in cell B20 of sheet 1, in cell A20 of that same sheet the value 1 must appear. These texts must be written in the range of cells from B20 to B25 and their values (quantities) in the previous cells.

So on sheet 2, if I write a text in cell B22 for example, and that text is in the range of cells B20: B25 on sheet 1, then the value on its left is due add 1.

This must be done successively on all the sheets and the value must be loaded automatically.

I attach example images:

< a href="https://i.stack.imgur.com/GS9qL.png">

I have tried ADDRESS, CELL and MATCH but I have not been able to get the expected result.

Can someone help me?

Thank you very much.

vba excel

asked by dani77 22.10.2018 в 21:21

source

1 answer

Problem with the bridge table in SQLite database HELP: Problems installing Laravel Collective

score 0 · Accepted Answer

Welcome to SO in Spanish.

You can not use VLOOKUP because this formula only returns values that are to the right of the search column. In your data, you use the team column as a search column and then you want to bring the value to the left, so it's not worth it.

You have to use MATCH and INDEX combined. My formula checks if the computer appears. If it does not appear, it returns 0. If it appears, then it will go the number on the left in the list, and add a 1.

=SI(CONTAR.SI($B$20:$B$23;E20)=0;0;1+INDICE($A$20:$B$23;COINCIDIR(E20;$B$20:$B$23;0);1))

I have put the data on the same page, but the formula serves you equally. You only have to update the ranges, which you have in different sheets. Nothing more.