SOLVED

PROCV and IF

Copper Contributor

Hi guys,

 

I have this table 1 in my excel, who that contains the information I'm looking for in the procv!!

rafi97cteep_0-1649782412028.png

 

I need to extract this information (which is in a tab) and bring it to another tab.

rafi97cteep_1-1649782476367.png

 

In the column "Tipo do Objeto" object type will be entered, and from that, a filter is performed on table 1 that will return variables that are related to this type, such as the part ("Parte Descrição") and the defect ("Tipo Descrição"), and each variable returns a number referring to the value contained in table 1

rafi97cteep_2-1649782775877.png

 

like that:

 

rafi97cteep_3-1649782850159.png

 

to do that i use some ifs =/ but however I know that I can using procv or even 2 procv... 

the problem is how do i do it! I've tried many ways and nothing...

 

help me guys!

 

 

 

 

 

6 Replies

@rafi97cteep 

=INDEX($B$2:$B$8,MATCH(1,($A$2:$A$8=N6)*($E$2:$E$8=Q6),0))
=VLOOKUP(N2&Q2,CHOOSE({1,2},$A$2:$A$8&$E$2:$E$8,$B$2:$B$8),2,0)

Is this what you want to do? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

@OliverScheurich 

 

Yes it is :)

 

But the text returns the number, that is, when looking for the "Parte Descrição" the procv must return "Parte Código"

Can you help me again?

Spoiler
Spoiler
rafi97cteep_0-1649850702268.png



like this:


rafi97cteep_1-1649850787071.png

 

 

sorry the spoilers
best response confirmed by rafi97cteep (Copper Contributor)
Solution

@rafi97cteep 

=VLOOKUP(H18,CHOOSE({1,2},$B$2:$B$8,$A$2:$A$8),2,FALSE)

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. It is also possible to look for the "Parte Descrição" and the "Tipo de Equipamento" and then return "Parte Código" with PROCV. There are different possibilities in the attached file.

 

If you work with Office365 or 2021 you can use XLOOKUP (PROCX) to do this. 

You can also use "Xlookup" instead. It is an alternate to Inde-x-Match.
Thanks ur the best
1 best response

Accepted Solutions
best response confirmed by rafi97cteep (Copper Contributor)
Solution

@rafi97cteep 

=VLOOKUP(H18,CHOOSE({1,2},$B$2:$B$8,$A$2:$A$8),2,FALSE)

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. It is also possible to look for the "Parte Descrição" and the "Tipo de Equipamento" and then return "Parte Código" with PROCV. There are different possibilities in the attached file.

 

If you work with Office365 or 2021 you can use XLOOKUP (PROCX) to do this. 

View solution in original post