Forum Discussion

gijsgans's avatar
gijsgans
Copper Contributor
Jul 21, 2021
Solved

search vertical and horizontal at the same time

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
     

 

 

 

 

 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jul 21, 2021

    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); "")

Resources