Values Table Search in excel

Navigation
1

Hello!

I'm doing a cost table for my company and I'm having to do a search on a table of values, but the only way I currently know how to do it is through multiple threaded SEs, which will give a lot of work, time and it will generate an incredible difficulty of maintenance or when it is to increase the table of values.

In a simpler way, I have the following:

Auxiliary Tables

CostTable(Whichrelatesthedata)

ThePNEU,DRAWINGANDWIDTHcolumnsaremade"by hand", according to what makes sense in the company logic, but the PERIMETER and WEIGHT / KILN columns should be generated automatically.

1) With few SEs I can make a formula for the PERIMETER column, the problem is that if the SIZE column of the Auxiliary Tables gets too large, it will not be feasible to do all this with Ses.

The logic here is simple: Return the PERIMETER value to a certain TIRE value. The SIZE and TIRE columns are the keys.

2) The same is for the WEIGHT / KILO column. In practice, there are various widths (n > 10) and various designs (n > 15).

The logic here is a slightly more advanced case, relative to "1": Return a WEIGHT / KILO value for a given value of DRAWING AND (logical operator) WIDTH. WIDTH is the key to the horizontal header of the auxiliary table and DRAWING is the key to the vertical header of the same.

I know how to do this in Java, but I do not know how to "write this in excel".

Thank you!

    
asked by anonymous 23.06.2017 / 22:58

2 answers

0

I suggest using PROCV() , like this: 

=PROCV(VALOR_PROCURADO;MATRIZ;COLUNA_RESULTADO;FALSO)

FALSE at the end is to bring the exact search result.

Another thing that can help you a lot is to use the formatting of Excel preformatted tables that goes help you a lot.

Then your formula would look like this:

  • Suppose we have two tabs, one with the data (name: data) and another auxiliary (name: tables), for the auxiliary tables, then the formula below would be in the data table, cell C2, that is, the first line in the Perimeter column: 

    =PROCV($A2;tabelas!$A$2:$B$3;2;FALSO)

    • Ready!

    The $ is to lock the column in case.

    If you use preformatted tables, it would be much easier, because when you add new data to an auxiliary table, you do not need to change the formula. Do some testing and you will see!

        
    24.06.2017 / 02:54
    0

    Many thanks Evert, it works that is a beauty.

    I used the pre-formatted table and looked like this: 

    =PROCV($C3;Tabela4;3;FALSO)

    I would also like to leave the following curiosity here: How to use procv with two conditions? 

    {=ÍNDICE(Tabela3[Peso/Metro];CORRESP(D3&E3;Tabela3[Desenho]&Tabela3[Largura];0)}

    Where the value I want is in the Weight / Meter column, d3 & e3 are the concatenated conditions and will be searched in the concatenated drawing & width columns.

    I found in: link

        
    29.06.2017 / 22:01
    Push in HTML5 with PHP After changing the Navigation Drawer icon it does not open anymore