Search for string of numbers within a range of columns and rows

meppwc

Active Member
Joined
May 16, 2003
Messages
409
I am struggling with creation of a formula that will perform the following:

On worksheet "Summary", search for the value found in B4 using worksheet "Table" range A2:F24, and if found on a given row, return the value in the 6th column of that row.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,876
Is there a specific column of A2:F24 where the value would be found.
If so, a VLOOKUP type formula would be the way to go.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,818
Office Version
365
Platform
Windows
Maybe this:

=INDEX($F$2:$F$24,AGGREGATE(14,6,(($A$2:$E$24=Summary!$B$4)*ROW($A$2:$E$24))-ROW($A$2)+1,1))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,606
Office Version
2007
Platform
Windows
If the value can be in any column, then try the following formula array


{=IFERROR(INDEX(Table!F2:F24,MAX(IF(-ISNUMBER(SEARCH(B4,Table!A2:F24)),ROW(F2:F24)))-1), "Not exist")}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.
 

Forum statistics

Threads
1,085,009
Messages
5,381,193
Members
401,718
Latest member
Ereese20

Some videos you may like

This Week's Hot Topics

Top