Can you use OFFSET with 1 match?

[3G]

Hello-

I have the following formula:


=MATCH(B6,Sheet1!$B$5:$B$14,0)


However, I want to pull back Column C from Sheet1 (In this case, C6). However, when I write the OFFSET, it tells me there's an error with the formula:


OFFSET(MATCH(B6,Sheet1!$B$5:$B$14,0),,1)


Can you use OFFSET with only 1 match, or, do you need to INDEX/MATCH it?


Thanks!

3G

 

[Hui]

The first parameter of Offset has to be a range or cell reference

Match returns a number not a range reference

That is why you have an error

 

[3G]

So would you do an "INDIRECT" then Hui, or, just index/match the same value twice, and, offset it by 1?

 

[Hui]

3G


Offset and Index do pretty much the same in regards to the fact that you can use numbers to reference the location of what area you want to return.


Indirect takes text and converts it to an address whuch can then be used as a reference.


In your case you haven't given us enough information to determine exactly what your after.

I think you want a cell from Column C, which matches a lookup in Column B

so you would use something like

=OFFSET(Sheet1!$C$5,MATCH(B6,Sheet1!$B$5:$B$14,0),0)

This assumes that you aren't on Sheet1 and are looking up the value in B6 on the sheet you are on