Searching multiple worksheets from an excel 2007 function
By : Dharmendra Kumar
Date : March 29 2020, 07:55 AM
help you fix your problem What you are really doing is a lookup. There is a VLOOKUP function built into Excel that will do exactly what you want. The syntax is code :
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP("20120101", Sheet2!A2:B1000, 2, false)
=VLOOKUP(DATEVALUE("20120101"), Sheet2!A2:B1000, 2, false)
Function myLookup(value, curr)
Dim dval As Long, luTable As Range, s As Worksheet, c As Range
' if user types date as string, convert it to date first...
If VarType(value) = vbString Then
dval = DateValue(value) ' this doesn't work if dval hasn't been declared as `long`!
Else
dval = value
End If
' see if `curr` is the name of a defined range; if so, use it
On Error GoTo notArange
' if the next line doesn't generate an error, then the named range exists:
Set luTable = Range(curr)
' so let's use it...
GoTo evaluateFunction
notArange:
' If we got here, "curr" wasn't the name of a range... it must be the name of a sheet
' first, tell VBA that we're done handling the last error:
Resume here
here:
On Error GoTo noSheet
Set s = ActiveWorkbook.Sheets(curr)
Dim firstCell As Range, lastCell As Range
Set firstCell = s.Range("a1")
Set lastCell = s.Range("b1").End(xlDown) ' assuming data in columns A and B, and contiguous
Set luTable = Range(firstCell, lastCell)
evaluateFunction:
myLookup = Application.WorksheetFunction.VLookup(dval, luTable, 2, False)
Exit Function
noSheet:
' get here if currency not found as either sheet or range > return an error message
myLookup = curr & " not found!"
End Function

Searching nonleftmost values with VLOOKUPlike function in Excel
By : Abhishek Majumdar
Date : March 29 2020, 07:55 AM
Does that help I have a question about excel function. , INDEX MATCH Combo You can use combination of 2 functions:

Excel substitute for FIND Function when searching for multiple criteria
By : creator
Date : March 29 2020, 07:55 AM
To fix this issue With you setup you would use INDEX/MATCH with your FIND as an array formuls: code :
=INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(FIND($C$1:$C$3,A1)),0))

Excel function for searching/matching/pasting data from different tables
By : Booji Boy
Date : March 29 2020, 07:55 AM
seems to work fine I have huge bank database from Bankscope which will be used in econometric analysis. I'm struggling how to associate bank statistics (table A, time period 9 years) with macroeconomic stats (table B, time period 9 years) from Eurostat. , In C2 type : code :
=IFERROR(INDEX($H$2:$J$7,MATCH($B2,$G$2:$G$7,0),MATCH(C$1,$H$1:$J$1,0)),"")

Is there an Excel function for copying all results without blank cells, while searching a match in another column
By : user3090750
Date : March 29 2020, 07:55 AM
will help you If you want to do this through formulas you could try the following: code :
=IFERROR(INDEX($B$1:$B$10,AGGREGATE(15,3,($A$1:$A$10=$D$2)*ROW($A$1:$A$10),COUNTIF($A$1:$A$10,"<>"&$D$2)+ROW(A1))),"")
=IF(A2=$E$2,ROW(),"")
=IFERROR(INDEX($B$1:$B$10,SMALL($C$2:$C$10,ROW(A1))),"")

