**Question description:**

*This user has given permission to use the problem statement for this blog.*

I'm trying to use offset in a match function. My table is pulling data from a different tab called "operator runs". I want my first row to look at data in B1:B104 for "sample-A" and match "BA" from C1:C104 to pull data from column D. My second row, I want to look up text from F1:F104 and match "BA" from G1:G104 and pull data from column H and so on, offset by 4 columns to the right each time. This is my current function. I don't know how to implement the offset function in this complicated function. =MATCH(1,('Operator runs'!$B$1:$B$104="Sample-A")*('Operator runs'!$C$1:$C$104="BA"),0)

Solved by O. L. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
27/03/2018 - 12:24

Hello

User
27/03/2018 - 12:25

hello

Excelchat Expert
27/03/2018 - 12:25

Welcome to excelchat

Excelchat Expert
27/03/2018 - 12:25

Do you have any sample data?

Excelchat Expert
27/03/2018 - 12:26

Where we can resolve your issue

User
27/03/2018 - 12:26

I don't have anything. I can enter some if needed

Excelchat Expert
27/03/2018 - 12:26

You want certain value to be returned for a particular value, isnt it?

User
27/03/2018 - 12:28

I understand the match function. I have a column B with Sample names. And column C with detectors (BA or A), and Column D with values. I have a formula to report out the value if I ask for Sample 1, BA. But then my next row down, I want to shift this function over by 4 columns. I've been having to manually change my reference from column B to column F, Column C to column G, Column D to Column H. I was wondering if Offset could be used

Excelchat Expert
27/03/2018 - 12:29

Can you put in some sample data so that I can help you better?

Excelchat Expert
27/03/2018 - 12:30

Ohhk, Now what are you expecting?

User
27/03/2018 - 12:32

Can you see my formulas? I'm trying to report out values based on the first data set in columns B-D for row 3 on sheet 2, then use the next data set in colums F-H on row 4 of sheet 2. But I don't want to manually change the reference

Excelchat Expert
27/03/2018 - 12:33

Yes let me check

User
27/03/2018 - 12:33

I thought offset might work, but I've never combined offset with a match function before

Excelchat Expert
27/03/2018 - 12:34

Lets try

Excelchat Expert
27/03/2018 - 12:36

= OFFSET ( MATCH ( lookup_value , lookup_array , 0 ) , MATCH ( lookup_value , lookup_array , 0 ) )

Excelchat Expert
27/03/2018 - 12:36

his is the syntax which we will have to use for offset plus match function

Excelchat Expert
27/03/2018 - 12:36

this*

User
27/03/2018 - 12:38

I'm not getting it to work

Excelchat Expert
27/03/2018 - 12:38

Kindly wait, its a bit tricky

Excelchat Expert
27/03/2018 - 12:38

*fingers crossed*

User
27/03/2018 - 12:38

ok, thanks

User
27/03/2018 - 12:38

thanks so much for your help

Excelchat Expert
27/03/2018 - 12:38

Thank me once we are done :)

Excelchat Expert
27/03/2018 - 12:45

HEY, IF YOU CAN CHANGE the way you have represented the data, things can get easy

User
27/03/2018 - 12:47

Unfortunately I can't change the way the data is represented. It's output from an instrument and the columns are automatically setup that way

User
27/03/2018 - 12:47

And the reason the data is split apart into three separate groups is that they are 3 separate outputs from the instrument.

User
27/03/2018 - 12:48

they have to stay separated.

Excelchat Expert
27/03/2018 - 12:48

The 4 is working now

Excelchat Expert
27/03/2018 - 12:48

:D

Excelchat Expert
27/03/2018 - 12:51

Actually offset matches the vertical and horizontal data

User
27/03/2018 - 12:51

I thought offset shifted your lookup by a specific number of rows or columns?

Excelchat Expert
27/03/2018 - 12:52

Offset match match is used when you need to perform a matrix type lookup

User
27/03/2018 - 12:52

how do you do the offset match match?

Excelchat Expert
27/03/2018 - 12:53

Just check the green box

Excelchat Expert
27/03/2018 - 12:53

the formula i have put in

Excelchat Expert
27/03/2018 - 12:53

Do you want me to explain it?

User
27/03/2018 - 12:54

What I want is to lookup my data in columns B-D, and then my next row down, I want to lookup my data in columns F-H, and then my next row down I want to lookup my data in columns J-L. That formula you have entered will not work. I can't setup my data like you have it.

Excelchat Expert
27/03/2018 - 12:54

I understand

Excelchat Expert
27/03/2018 - 12:56

Lets try something else

Excelchat Expert
27/03/2018 - 12:58

Can you atleast put the data in one line?

User
27/03/2018 - 12:58

The data has to be how I have it on Sheet 1, unfortunately

Excelchat Expert
27/03/2018 - 01:00

SInce you data is not in a unform way, I dont really thing I can help

Excelchat Expert
27/03/2018 - 01:00

Ill try one last time

User
27/03/2018 - 01:01

ok.

Excelchat Expert
27/03/2018 - 01:01

I am sorry to disappoint you

User
27/03/2018 - 01:01

that's ok, it's super hard!

User
27/03/2018 - 01:01

and annoying the way the data is setup

Excelchat Expert
27/03/2018 - 01:01

Absolutely

Excelchat Expert
27/03/2018 - 01:01

Multiple conditioners will have to be applied

Excelchat Expert
27/03/2018 - 01:01

maybe it can be solved

User
27/03/2018 - 01:02

I'll keep working on it. Thanks for trying

Excelchat Expert
27/03/2018 - 01:02

Sorry to disappoint you mate

User
27/03/2018 - 01:02

no worries. Thanks

User
27/03/2018 - 01:02

bye

Excelchat Expert
27/03/2018 - 01:03

Just try multiple logical tests in excel

Excelchat Expert
27/03/2018 - 01:03

ybe it can help you somehow

Excelchat Expert
27/03/2018 - 01:03

Take care