CODEWITHSUNDEEP
excelexcel-formula
James
James

Reputation: 55

Checking if a value exists anywhere in range in Excel

I want to check if the value of the cell A1 exist anywhere from sheet2!$A$2:$z$50.

IF the value exist then return the value of the 1st Row at the column where the match was found.

I tried:

but this functions are limited to check if match at a single row / column.

I was hoping for something like =IF(A1,sheet2!$A$2:$Z$50,x1,FALSE) where x = the column where the match was found.

Is there something like that?

Upvotes: 2

Views: 8383

Answers (4)

user13837190
user13837190

Reputation: 1

My favourite for returning a value from a lookup sheet if a corresponding cell exists in both the lookup sheet and the sheet you want to enter data into is

INDEX('Sheet1'!I:I,(MATCH(D2,'Sheet1'!A:A,0)))

This is basically a more flexible version of a Vlookup or Match If formula. It say is you find a value matching what's in D2 in the Column A:A of Sheet 1 then return the corresponding value (from the same row) from Column I:I of Sheet 1.

I use this if I want to match data between two sheets e.g. pricing matched on product names or SKU.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

Say Sheet2 is like:

enter image description here

We want a formula on Sheet1 that will return the value in the header row if that column contains a value to be found. So if A1 contains Good Guy then the formula should return Victor Laszlo

Put the following UDF in a standard module:

Public Function GetHeader(v As Variant, rTable As Range) As Variant
    Dim rHead As Range, rData As Range, WhereIsIt As Range
    Set rHead = Intersect(rTable(1).EntireRow, rTable)
    Set rData = Intersect(rTable.Offset(1), rTable)

    Set WhereIsIt = rData.Find(what:=v, After:=rData(1))
    If WhereIsIt Is Nothing Then
        v = "NOT FOUND"
        Exit Function
    End If

    GetHeader = Intersect(WhereIsIt.EntireColumn, rHead).Value
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=GetHeader(A1,Sheet2!A1:Z50)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

NOTICE:

We give the UDF the entire range including the header row (although the header row is excluded from the search)

Upvotes: 0

brettdj
brettdj

Reputation: 55702

An array formula like this would work

Press ShiftCtrlEnter together

=IF(COUNTIF(A2:Z50,A1)>0,MIN(IF(A2:Z50=A1,ROW(A2:Z50),51)),"not found")

Upvotes: 3

Brian L
Brian L

Reputation: 3251

I think you can do it if you use helper cells.

  • In a helper row, use MATCH() in exact mode on each column to find the row index of the value in that column (if it exists). Wrap that in an NOT(ISERROR()) to turn the result into true/false binary data. TRUE = match; FALSE = no match.
  • Then use MATCH() again on the helper row, this time finding the first TRUE (i.e. matching) column; MATCH() will return the index of the column; then you can use INDEX() to look up the value of the first row in that column.

Upvotes: 0

Related Questions