# CSE formula help

#### konew1

##### Well-known Member
I have a list of data that has names in column A, numbers in column B and other numbers in C:Z. I want to extract data from columns A & B from one row only.
I have the formula
{=OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0)} entered as array formula that correctly returns the name "Pacey"
Formula {=OFFSET(B13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0)} returns "29".
If i create {=OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0)&OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0)} it gives "PaceyPacey". It is what I expect from the formula but not what I need.

If I create {=OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0)&OFFSET(B13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0) } i get #Value!
Likewise =OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0)&OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),1) gives #value. The expected result is "Pacey29"

What have I got wrong?

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi,

Are you sure you remember to confirm the formula with Ctrl + Shift + Enter? If not you will get the #value! error.

If that is not the case, try to debug the formula by selecting parts of it and press F9. Thus you should see which part gives the error.

Thanks. Every part of the formula returned #value when tested with F9, but I have discovered the problem was the attempt to concatenate text to a number. That never works unless the number is converted. I was looking for a complex problem when it was simple.
=OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),0)&TEXT(OFFSET(A13,MATCH(C207,(J13:J204)*(K13:K204)-(O13:O204)-(P13:P204),0),1),"0")

Replies
0
Views
830
Replies
6
Views
225
Replies
1
Views
177
Replies
6
Views
236
Replies
3
Views
122

Threads
1,203,605
Messages
6,056,239
Members
444,852
Latest member
MJaspering

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back