SOLVED

search vertical and horizontal at the same time

%3CLINGO-SUB%20id%3D%22lingo-sub-2569297%22%20slang%3D%22en-US%22%3Esearch%20vertical%20and%20horizontal%20at%20the%20same%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2569297%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20search%20for%20a%20value%20in%20Colom%20A.%20For%20example%20the%20found%20value%20is%20in%20cell%20A2%2C%20I%20want%20the%20value%20of%20the%20cel%202kolom%20right%20and%202%20rows%20down%20(cell%20C4)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20table%20below%2C%20so%20when%20i%20will%20do%20a%20vertical%20lookup%20for%20%22jan%22%20i%20want%20a%20return%20of%20%22groen%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWho%20can%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2249.447513812154696%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2212.265193370165747%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%228.535911602209943%25%22%3Ea%3C%2FTD%3E%3CTD%20width%3D%229.08839779005525%25%22%3Eb%3C%2FTD%3E%3CTD%20width%3D%2210.055248618784539%25%22%3Ec%3C%2FTD%3E%3CTD%20width%3D%229.502762430939221%25%22%3Ed%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.265193370165747%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%228.535911602209943%25%22%3Ejan%3C%2FTD%3E%3CTD%20width%3D%229.08839779005525%25%22%3Ehuis%3C%2FTD%3E%3CTD%20width%3D%2210.055248618784539%25%22%3Erood%3C%2FTD%3E%3CTD%20width%3D%229.502762430939221%25%22%3Eappel%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.265193370165747%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%228.535911602209943%25%22%3Ebert%3C%2FTD%3E%3CTD%20width%3D%229.08839779005525%25%22%3Eboot%3C%2FTD%3E%3CTD%20width%3D%2210.055248618784539%25%22%3Eblauw%3C%2FTD%3E%3CTD%20width%3D%229.502762430939221%25%22%3Ebanaan%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.265193370165747%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%228.535911602209943%25%22%3Ehans%3C%2FTD%3E%3CTD%20width%3D%229.08839779005525%25%22%3Eauto%3C%2FTD%3E%3CTD%20width%3D%2210.055248618784539%25%22%3Egroen%3C%2FTD%3E%3CTD%20width%3D%229.502762430939221%25%22%3Epeer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.265193370165747%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%228.535911602209943%25%22%3Eerik%3C%2FTD%3E%3CTD%20width%3D%229.08839779005525%25%22%3Emotor%3C%2FTD%3E%3CTD%20width%3D%2210.055248618784539%25%22%3Egeel%3C%2FTD%3E%3CTD%20width%3D%229.502762430939221%25%22%3Emeloen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.265193370165747%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%228.535911602209943%25%22%3Ebassie%3C%2FTD%3E%3CTD%20width%3D%229.08839779005525%25%22%3Ecaravan%3C%2FTD%3E%3CTD%20width%3D%2210.055248618784539%25%22%3Epaars%3C%2FTD%3E%3CTD%20width%3D%229.502762430939221%25%22%3Edruif%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.265193370165747%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%228.535911602209943%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%229.08839779005525%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210.055248618784539%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%229.502762430939221%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2569297%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2569790%22%20slang%3D%22en-US%22%3ERe%3A%20search%20vertical%20and%20horizontal%20at%20the%20same%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2569790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1108731%22%20target%3D%22_blank%22%3E%40gijsgans%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(C1%3AC5%2C%20MATCH(%22jan%22%2C%20A1%3AA5%2C%200)%2B2)%2C%20%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20use%20comma%20as%20decimal%20separator%2C%20use%20%3B%20instead%20of%20%2C%20in%20the%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(C1%3AC5%3B%20MATCH(%22jan%22%3B%20A1%3AA5%3B%200)%2B2)%3B%20%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2569870%22%20slang%3D%22en-US%22%3ERe%3A%20search%20vertical%20and%20horizontal%20at%20the%20same%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2569870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThanks%20you%20very%20much!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I want to search for a value in Colom A. For example the found value is in cell A2, I want the value of the cel 2kolom right and 2 rows down (cell C4)

 

See table below, so when i will do a vertical lookup for "jan" i want a return of "groen". 

 

Who can help me?

 

 abcd
1janhuisroodappel
2bertbootblauwbanaan
3hansautogroenpeer
4erikmotorgeelmeloen
5bassiecaravanpaarsdruif
     

 

 

 

 

 

2 Replies
best response confirmed by gijsgans (New Contributor)
Solution

@gijsgans 

 

=IFERROR(INDEX(C1:C5, MATCH("jan", A1:A5, 0)+2), "")

 

If you use comma as decimal separator, use ; instead of , in the formula:

 

=IFERROR(INDEX(C1:C5; MATCH("jan"; A1:A5; 0)+2); "")

@Hans Vogelaar Thanks you very much!