• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match in Multiple sheet

vasim

Member
Hello,


I have a few set of sheets; I want a match formula in cell F1 where the lookup array could be in any sheet. Attached is something which I tried to do but vain.

I tried to set x as a range and then take that range in between the match formula.


Any help appreciated.


Sub step()

Dim ws As Worksheet

Dim x As Range


For Each ws In Worksheets

If ws.Name = Range("B1").Value Then ws.Select

Next


Set x = ActiveSheet.Range("A:A")


Sheets("Sheet1").Select


'With ActiveSheet


Range("F1").Select

ActiveCell.FormulaR1C1 = "=MATCH(RC[-5],x!C[-5])"


End Sub


Regards,

Indian
 

vasim

Member
Thankssss Hui,


Really sorry I'm in office now, not much of the sites works.....to simplfy it further I want a match formula in cell F1 which look up the value of A5 in different sheets. (the lookup range can be any of the sheets within the workbook.)


Appreciated your help....


Regards,

Indian
 

Hui

Excel Ninja
Staff member
When you say looks up

Do you want the sheet it us on, or a

Corresponding record ?

What if there is multiple matches?
 

vasim

Member
Thanks Hui,


Suppose there are 5 sheets SHeet1, sheet2, sheet3 and so on....


Forget the above part: I want a formula in sheet1 as


C1= Match(A1,B1,0)

where B1 is sheet2, sheet3, sheet4 and column needs to be A:A


There are no multiple records


Please shout if the this is still not upto the mark..


Regards,

Indian
 

vasim

Member
No the row number on what sheet (B1) it is....I have some ahead condition based on this


The same data can be on two different sheet but not on the same sheet...
 

Hui

Excel Ninja
Staff member
This Formula will do that:

=SUMPRODUCT((INDIRECT(B1&"!A:A")=B2)*ROW(INDIRECT(B1&"!A:A")))

where

B1 is the Sheet Name

B2 is the Value your looking for
 

vasim

Member
Thanks Hui,


=MATCH(B2,INDIRECT(B1&"!A:A"),0) was what I was looking for.... you are simply great....
 
Top