CODEWITHSUNDEEP
excelexcel-formula
Ish Thomas
Ish Thomas

Reputation: 2440

How to search for a value in the excel sheet using formula?

I'm not talking about VBScript. I'd like to get a cell or at least a row number after looking for particular string in the whole sheet.

Nothing works for me.. I was trying:

=MATCH("*LookingForThis*", A1:A3&B1:B3,0)

But that doesn't work.

Just to be sure, I was clear. I have a sheet like:

Foo1 Foo2 Foo3 Foo4 Foo5

Bar1 Bar2 Bar3 Bar4 Bar5

Tst1 Tst2 Tst3 Tst4 Tst5

When someone search for "Bar2" - the result will be "1" (as row #2), or a cell. But I need just a row. Need some help with this.

Thanks,

Upvotes: 0

Views: 43

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

I prefer the Aggregate function for something like this:

=AGGREGATE(15,6,ROW($A$1:$E$3)/($A$1:$E$3=G1),1)

enter image description here

You then can subtract the desired amount to get the correct index.

Upvotes: 1

Related Questions