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
