Complex Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-3302020%22%20slang%3D%22en-US%22%3EComplex%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302020%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EInterest%20issue%20it%20will%20be%20great%20to%20hear%20solution%20for%20my%20problem%20.%3C%2FP%3E%3CP%3E2%20Tables%20%3A%3C%2FP%3E%3CP%3E1.%20One%20of%20them%20with%20rows%2C%20and%20one%20of%20them%20with%20columns%3C%2FP%3E%3CP%3E2.%20Need%20to%20compare%20the%20%23%20and%20Type%20columns%20between%20the%20tables%20and%20insert%20the%20correct%20%23%20from%20table%20B%20to%20table%20A%20(Insert%20the%20column%20data%20table%26nbsp%3B%20into%20the%20row%20data%20table%3C%2FP%3E%3CP%3E3.%20For%20example%20attached%20data%20sheet%20with%20the%202%20tables%20.%3C%2FP%3E%3CP%3Eit%20will%20be%20great%20to%20receive%20a%20formula%20for%20that%20problem%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22379%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2291%22%3ETable%20B%3C%2FTD%3E%3CTD%20width%3D%2272%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%23%3C%2FTD%3E%3CTD%3ETopic%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.001%3C%2FTD%3E%3CTD%3EBall%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.002%3C%2FTD%3E%3CTD%3EChair%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.001%3C%2FTD%3E%3CTD%3EFloor%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E0.5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.002%3C%2FTD%3E%3CTD%3EBoard%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E1000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETable%20A%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%23%3C%2FTD%3E%3CTD%3ETopic%3C%2FTD%3E%3CTD%3EType%3C%2FTD%3E%3CTD%3EDesc%3C%2FTD%3E%3CTD%3EPrice%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.001%3C%2FTD%3E%3CTD%3EBall%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.001%3C%2FTD%3E%3CTD%3EBall%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.002%3C%2FTD%3E%3CTD%3EChair%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.002%3C%2FTD%3E%3CTD%3EChair%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.001%3C%2FTD%3E%3CTD%3EFloor%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.001%3C%2FTD%3E%3CTD%3EFloor%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.002%3C%2FTD%3E%3CTD%3EBoard%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.002%3C%2FTD%3E%3CTD%3EBoard%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3302020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302265%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302265%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1214958%22%20target%3D%22_blank%22%3E%40Isra9632%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E's%20formula%20is%20correct%2C%20but%20you%20have%20to%20adjust%20the%20ranges%20and%20cell%20references%20to%20match%20your%20actual%20layout%2C%20of%20course.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302251%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1214958%22%20target%3D%22_blank%22%3E%40Isra9632%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22560%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22112%22%3ETable%20A%3C%2FTD%3E%3CTD%20width%3D%22112%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22112%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22112%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22112%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%23%3C%2FTD%3E%3CTD%3ETopic%3C%2FTD%3E%3CTD%3EType%3C%2FTD%3E%3CTD%3EDesc%3C%2FTD%3E%3CTD%3EPrice%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23DF0000%22%3E01.07.00.001%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3EBall%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23DF0000%22%3E1005%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23DF0000%22%3E5%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%23DF0000%22%3E01.07.00.001%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3EBall%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23DF0000%22%3E1006%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%23DF0000%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.002%3C%2FTD%3E%3CTD%3EChair%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.07.00.002%3C%2FTD%3E%3CTD%3EChair%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.001%3C%2FTD%3E%3CTD%3EFloor%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.001%3C%2FTD%3E%3CTD%3EFloor%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E0%2C5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.002%3C%2FTD%3E%3CTD%3EBoard%3C%2FTD%3E%3CTD%3E1006%3C%2FTD%3E%3CTD%3EU.Source%3C%2FTD%3E%3CTD%3E1000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01.08.02.002%3C%2FTD%3E%3CTD%3EBoard%3C%2FTD%3E%3CTD%3E1005%3C%2FTD%3E%3CTD%3EMaterial%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20highlighted%20the%20exemplary%20results%20for%20%23%20and%20Type%20red.%20The%20formula%20returns%20the%20expected%20results%205%20and%2010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(%24C%243%3A%24D%246%2CMATCH(1%2C(A11%3D%24A%243%3A%24A%246)*(B11%3D%24B%243%3A%24B%246)%2C0)%2CMATCH(C11%2C%24C%242%3A%24D%242%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAlternatively%20you%20can%20apply%20another%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(A11%26amp%3BB11%2CCHOOSE(%7B1%2C2%2C3%7D%2C%24A%243%3A%24A%246%26amp%3B%24B%243%3A%24B%246%2C%24C%243%3A%24C%246%2C%24D%243%3A%24D%246)%2CMATCH(C11%2C%24C%242%3A%24D%242%2C0)%2B1%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EEnter%20both%20formulas%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%26nbsp%3B%202021.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302227%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302227%22%20slang%3D%22en-US%22%3EThx%20Quadruple%3CBR%20%2F%3EIts%20still%20not%20working%3CBR%20%2F%3EI%20will%20try%20to%20define%20my%20request%20again%20%3A%3CBR%20%2F%3E1.%20Compare%20the%202%20tables%3CBR%20%2F%3E2.%20Table%20A%20%3A%20Type%20field%20is%20by%20row%20(and%20not%20by%20column-%20like%20table%20b).%3CBR%20%2F%3EI%20need%20to%20receive%20a%20result%20at%20the%20price%20field%20by%20rows%20%2C%20for%20example%20%3A%3CBR%20%2F%3E01.07.00.001%20-%201005%20%3A%20result%20should%20be%20%2C%205%3CBR%20%2F%3E01.07.00.001-1006%20%3A%20result%20should%20be%20%2C10%3CBR%20%2F%3E%3CBR%20%2F%3Eetc'%20-%20hope%20it's%20more%20clear%20now%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302067%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1214958%22%20target%3D%22_blank%22%3E%40Isra9632%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(%24C%244%3A%24D%247%2CMATCH(1%2C(A12%3D%24A%244%3A%24A%247)*(B12%3D%24B%244%3A%24B%247)%2C0)%2CMATCH(C12%2C%24C%243%3A%24D%243%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

Interest issue it will be great to hear solution for my problem .

2 Tables :

1. One of them with rows, and one of them with columns

2. Need to compare the # and Type columns between the tables and insert the correct # from table B to table A (Insert the column data table  into the row data table

3. For example attached data sheet with the 2 tables .

it will be great to receive a formula for that problem 

 

Table B    
#Topic10051006 
01.07.00.001Ball510 
01.07.00.002Chair820 
01.08.02.001Floor100.5 
01.08.02.002Board501000 
     
     
Table A    
#TopicTypeDescPrice
01.07.00.001Ball1005Material 
01.07.00.001Ball1006U.Source 
01.07.00.002Chair1006U.Source 
01.07.00.002Chair1005Material 
01.08.02.001Floor1005Material 
01.08.02.001Floor1006U.Source 
01.08.02.002Board1006U.Source 
01.08.02.002Board1005Material 
4 Replies

@Isra9632 

=INDEX($C$4:$D$7,MATCH(1,(A12=$A$4:$A$7)*(B12=$B$4:$B$7),0),MATCH(C12,$C$3:$D$3,0))

Is this what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

Thx Quadruple
Its still not working
I will try to define my request again :
1. Compare the 2 tables
2. Table A : Type field is by row (and not by column- like table b).
I need to receive a result at the price field by rows , for example :
01.07.00.001 - 1005 : result should be , 5
01.07.00.001-1006 : result should be ,10

etc' - hope it's more clear now

@Isra9632 

Table A    
#TopicTypeDescPrice
01.07.00.001Ball1005Material5
01.07.00.001Ball1006U.Source10
01.07.00.002Chair1006U.Source20
01.07.00.002Chair1005Material8
01.08.02.001Floor1005Material10
01.08.02.001Floor1006U.Source0,5
01.08.02.002Board1006U.Source1000
01.08.02.002Board1005Material50

 

I've highlighted the exemplary results for # and Type red. The formula returns the expected results 5 and 10.

 

=INDEX($C$3:$D$6,MATCH(1,(A11=$A$3:$A$6)*(B11=$B$3:$B$6),0),MATCH(C11,$C$2:$D$2,0))

Alternatively you can apply another formula:

 

=VLOOKUP(A11&B11,CHOOSE({1,2,3},$A$3:$A$6&$B$3:$B$6,$C$3:$C$6,$D$3:$D$6),MATCH(C11,$C$2:$D$2,0)+1,0)

Enter both formulas with ctrl+shift+enter if you don't work with Office365 or  2021.

 

 

@Isra9632 

@Quadruple_Pawn's formula is correct, but you have to adjust the ranges and cell references to match your actual layout, of course.

See the attached sample workbook.