• 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.

VLOOKUP if value appears in any column /or IF(OR( ???

Hi, I just changed them to be absolute and still no joy Im afraid, yes most of them should exist in the data but its just returning not found for all
 
tarynmahon,

Did you put the VB in a new module, or it just within a sheet module?


I tried setting up my workbook using the same sheet names, and everything was working.
 
Hmm. Looking at my code, I may have restricted things too much by trying to force the inputs to be specific things. Try using this one:

[pre]
Code: 
Function BigLookup(xFind As Variant, SearchRange As Range, xOutput As Variant)
'set the default value
BigLookup = ""

'IMPORTANT!!!
'Change the list of sheets as needed
For Each sh In Worksheets(Array("BWApr", "BWMay", "BWJun"))
For Each c In sh.Range(SearchRange.Address)
If xFind = c Then
BigLookup = xOutput
GoTo ValueFound
End If
Next c
Next sh
ValueFound:
'Hurrah!
End Function
[/pre]
 
That means either you didn't put it in the correct module, or you have two functions with the same name. To clarify my post from above, you will need to delete the previous code that I provided and paste in this new one.
 
Tarynmahon,

Regarding your earlier comment regarding the IF(..) formula, could you also confirm that the data types in xdh1 is of the same type as in the data range?


MATCH() function expects the same data type in the lookup value and lookup range.


Hope this helps.


Cheers,

Sajan.
 
Sajan-

Not sure what you mean by data types?

The value that I'm looking up is actually SDLAG_AT1 and only appears in column AC in sheets BWApr, BWMay, BWJun

I was thinking maybe only referencing this one column AC12:AC1385 rather than A12:AK13285

The other columns have all sorts of data in, ie. Dates, Values, Names
 
Great to hear that! Hopefully, you got two solutions for your problem!! The UDF based and the formula based solutions.
 
Amazing Sajan, thank you soooo much for that!

Thank you Luke as well for all your help it's really appreciated, and given me my first insight into VB!!!


I cant thank you both enough!
 
tarynmahon


FYI, MATCH will not work with multiple dimension lookup_array. It must be single dimension, ie: A:A, 1:1. You can't use like A:B, 1:3 etc..


...but you can use COUNTIF instead.


=IF(SUM(COUNTIF(INDIRECT("BW"&{"Apr","May","Jun"}&"!A12:AK13285"),XDH1)),XDH1,"Not")


If you don't want to use volatile INDIRECT, use each COUNTIF for per sheet.
 
You must log in or register to reply here.
Back
Top