How to use Excel’s "PROCH" function to fetch values from the bottom up?

Asked

Viewed 1,621 times

0

By default the "Proch" function only looks for values from top to bottom, I intend to do the inverse, fetch the values from bottom to top, I want to search for the "40-D" character through the "Cod.Prod" reference, as I proceed through the "Proch" function?? or there is another function that does this procedure??inserir a descrição da imagem aqui

excel

  • the value you seek and always what is above the Cod.Prod?

  • 1

    Yes! There’s some way to do it??

2 answers

1


@Elienay taking the table you present and always having the value above Cod.Prod uses the following formula in the cell G44.

=+DESLOCAMENTO(B48;-1;CORRESP(F44;C48;0);1;1)

EDITION

As you can see the formula has been developed according to your initial table.

Table: C44:E48
Value to look for: F44
Formula cell: G44

inserir a descrição da imagem aqui

  • Could you specify the address of the cells according to the image I posted? is because I never used this 'shift' function and left me a little confused, because the cells you typed do not match the picture table, could?

  • I died trying here, but I did not find the solution with this formula kkk, try to specify the address right, OK?

  • @Elienay, the function matches the data in your table. Have you tried copying the formula and pasting? Instead of DESLOCAMENTO trial DESLOC.

  • Now that I saw understand, my doubt was that address 'B48' that is being directed to an empty cell, because it works as a reference to the cells to the right always looking for the value to the right and adding +1 to a higher cell.

1

It is not possible, as the logic of PROCH is from top to bottom, just as PROCV is from left to right, always from the first line, to PROCH, or first column, to PROCV.

A solution for your case is the use of INDEX + CORRESP

Imagem de exemplo

But if it’s worth a comment, it doesn’t make sense to work that way with the column names below. I suggest standardizing the table instead of following it this way.

  • I couldn’t locate with this formula, when I type "Cod.Prod" it fetches the same value "Cod.Prod" it should fetch "40-D"

  • But you don’t have to type "Cod.Prod", you have to specify which product code you want to look for. In the example I searched for the information concerning Cod.Prod "40-D".

  • What I wanted was to search in sequence, for example: when typing "Cod.Prod" he would bring me the "40-D" and if I typed "40-D" he would bring me the "30-C" and so on

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.