Im in over my head

[doveyp]

Can somene please help me with the following: if cell b16=any cell in the range f16:j16 then display the cell in column k that is in the same row as the match? If no match in the range please display nothing. Thankyou in advance.

 

[Glory]

=if( iserror( if( match(b16,f16:j16,0) ,b16,"") ) ,"",b16)

 

[doveyp]

Thankyou Glory why does my return show nothing when I know the text in b16 is in the range f15:j16? Actually the first post should have said range f15:j16 not f16:j16.

 

[jasonb75]

Try

=IF(ISNUMBER(MATCH(B16,F15:J15,0)),K15,IF(ISNUMBER(MATCH(B16,F16:J16,0)),K16,""))

 

[Glory]

It looks like the method I chose doesn't work with multiple rows. The formula works, but only with a single row for some reason.

Someone will come up with a solution for you. Here's a simpler version btw, some of the previous formula was superfluous:

=if( iserror( match(b16,f16:j16,0) ) ,"",b16)


Edit: Jason, why is it necessary to work with the rows one at a time?

 

[jasonb75]

The match and lookup functions can only search a single row or column, there are workarounds to this using array formula, but with only 2 rows, this method is more practical.

 

[Glory]

Are there other functions that let you use multiple rows and columns more directly? Why can't the function just treat each new row/column like a new dimension in an array?

 

[doveyp]

Hi Jason,

Thankyou so much that works perfectly. My real use for the formula is on a much larger scale can you provide me with the workaround you mentioned for a larger range?

 

[doveyp]

Hi Glory,

Thankyou for the effort and time you have spent on my problem.

 

[jasonb75]

One way to use a workaround is

=INDEX($K$15:$K$16,MIN(IF($F$15:$J$16=B16,(ROW($K$15:$K$16)-ROW($K$15))+1)))

The formula must be array confirmed using Shift Ctrl Enter.