return a value from a search across multiple columns

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
48,294
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What is the Match formula you already have?
 

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,294
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
ooo thank you, that worked. I tried using index but got N/A. thankyou.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,294
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
 

buroh

New Member
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,335
Messages
5,547,336
Members
410,785
Latest member
phillippaige
Top