SOLVED

search vertical and horizontal at the same time

Copper 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 (Copper 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! 

1 best response

Accepted Solutions
best response confirmed by gijsgans (Copper 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); "")

View solution in original post