# return a value from a search across multiple columns

#### buroh

##### New Member
I have searched and can't find what I am looking for, so I had to post this question.

I have a spreadsheet (not mine, it is a shared one created by someone else), it has columns/rows A1 - AW850.
Not every column is populated, but the further most ones are.

So, I need to return the value of the 1st column of 3 which the 3rd column is the value I am looking for.

for example:

Row 185
Columns:
AU AV AW
P02 Text 20.07.20

so in the example above: my match formula will find the value 20.07.20 in column AW185, but I need the formula to return the value of the cell that is 2 cells left of the matching value; which would be "P02" in column AU185

but not every column is populated, so AW in row 185 would have 20.07.20, but row 186, column S would be populated with 20.07.20, and so on.

the match formula works to find 20.07.20, but I am stuck on how to return the P02 value.
the P value will always be 2 cells left of the date in every row.

any help would be appreciated.

Wayne

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
What is the Match formula you already have?

#### buroh

##### New Member
Hi & welcome to MrExcel.
What is the Match formula you already have?

=ISNUMBER(MATCH("20.07.20",C185:AW185,0))

#### buroh

##### New Member
Hi & welcome to MrExcel.
What is the Match formula you already have?

That just returns a True value to say that in the entire row, it has found the value 20.07.20

#### Fluff

##### MrExcel MVP, Moderator

Ok, try
=INDEX(C185:AW185,MATCH("20.07.20",C185:AW185,0)-2)

#### buroh

##### New Member
ooo thank you, that worked. I tried using index but got N/A. thankyou.

#### Fluff

##### MrExcel MVP, Moderator

You're welcome & thanks for the feedback.

#### buroh

##### New Member
You're welcome & thanks for the feedback.
Hello,

Thank you for your help in solving my formula issue, I am back, because I tried using this formula in another spreadsheet that is not mine and I get #N/A return from the formula above.

Is there a limit to how big the array can be in the formula for searching?

Wayne

#### buroh

##### New Member
You're welcome & thanks for the feedback.
Sorry, ignore my last reply, I figured it out. I was using \$ in front of my array, I took out the \$ and it worked.

Replies
2
Views
481
Replies
2
Views
45
Replies
5
Views
69
Replies
5
Views
125
Replies
5
Views
234