return a value from a search across multiple columns

[buroh]

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

 

[Fluff]

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

 

[buroh]

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

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

 

[buroh]

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]

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

 

[buroh]

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

 

[Fluff]

You're welcome & thanks for the feedback.

 

[buroh]

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]

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.