• 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( ???

tarynmahon

tarynmahon

Member
[pre]
Code: 
A,  B,  C,  D,  E,         Lookup Ref
129 123 126 130 Bread      - 123
122 123 122 122 Orange     - 122
120 118 124 127 Grape      - 121
119 119 123 124 Mars       - 120
[/pre]

So, in column F, I want to look up the "Lookup Ref" and return the value in column E, if the "Lookup Ref" appears in either column A,B,C or D


Im assuming it will be more of an =IF(OR( Formula than a lookup but not sure how to write it when a whole column of data is involved
 
Hi, tarynmahon!

Could you please clarify what values should be displayed in F2:F5? Thanks.

Regards!
 
I think in F2:

Code: 
=IFERROR(IF((MATCH(VALUE(RIGHT(E2,LEN(E2)-FIND(" - ",E2)-2)),A2:D2,0)),E2,""),"")

Copy down
 
@Montrey

Hi!

And a so dumb guy didn't understood that, ha ha ha...

Regards!

PS: Ooops... it was me!!!
 
Assuming Lookup Ref in G2:G5


Try this in F2 and copy down.


=IF(COUNT(LOOKUP(9E+300,SEARCH("-"&A2:D2&"-","-"&G2&"-")/(A2:D2<>""))),E2,"")
 
Brilliant Haseeb A that worked for me, unfortunately Hui yours didnt but I think thats probably down to the way that I wrote the Lookup Ref


Thank you all for your help :)
 
Ahhh a problem, in my example above, I simplified the data in order to understand any answers I was given but instead of there being multiple columns to look up ie. A2:D2, it's actually different sheets on the same workbook, how would I account for that please?

Also, there is no (-) I was just using that so you wouldnt think there was more data in column F but that was easy enough to take out.


All help to a poor defenseless young lady much appreciated :)
 
Working with multiple sheets will get tricky as there are a limited number of functions that can handle 3D references (references that go through multiple sheets). Any chance you can somehow change the layout so that all the data is on one sheet? Or, perhaps we can use some formulas to pull all the info onto one sheet? If the latter, we'll need to know if all the data is in the same cell position on each sheet.
 
If you're okay with using an UDF, I think this will work. First, open a new module in VBE (right click on sheet tab, view code, Insert - Module). Paste the following in:

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

'IMPORTANT!!!
'Change the list of sheets as needed
For Each sh In Worksheets(Array("Sheet4", "Sheet2", "Sheet3"))
For Each c In sh.Range(SearchRange.Address)
If xFind = c Then
BigLookup = xOutput.Value
GoTo ValueFound
End If
Next c
Next sh
ValueFound:
'Hurrah!
End Function
[/pre]
Pay attention to the line I marked important, as you'll need to change the list of sheet names as fits your workbook. It's just a list of worksheet names, so feel free to add more (following the format pattern) as needed.

Then, in your workbook, formula is something like:

=BigLookup(FindThis,A2:AK2,OutputValue)
 
I dont know the first thing about VBE, the only time I have ever had anything to do with Macros is when I clicked on "Record Macro" then edit, then I closed it again as it looked too complicated!

I will give your instructions a go though and see how I get on (Fingers Crossed)


Thanks
 
Tarynmahon


The easiest way to get an answer is to upload a sample file with sample data so that we know exactly what format your data is in


You get an answer quicker and we don't waste our time guessing what you want and can then help others...


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
tarynmahon,


Don't let VB frighten you off. For just copying the code in, we can set it up fairly easily.

Starting in your workbook.

1. Right-click on sheet tab, click on "view code". This will open the Editor.

2. From the menu, click on Insert, then on Module.

3. Paste the code that I posted above into the module

4. You'll probably need to change this line of code (or, list the names of all the worksheets you are using, and someone here can write it)

[pre]
Code: 
For Each sh In Worksheets(Array("Sheet4", "Sheet2", "Sheet3"))
[/pre]
You'll need to list each sheet name within the inner parentehsis. So, if your sheet names were Larry, Curly, Moe, and Fred, the code would be:

For Each sh In Worksheets(Array("Larry", "Curly", "Moe", "Fred"))


5. Close the Editor

6. In your workbook, you can now type your new user defined function (UDF) just like a regular formula. It will be something like:

=BigLookup(FindThis,A2:AK2,OutputValue)


Replace "FindThis" and "OutputValue" with the correct cell references.
 
Hmm. Could you post the formula that you wrote?

You made sure that all of the sheet names you need to use (and none that aren't) were listed in the code?
 
tarynmahon,

You could also try the following:


Assuming that your worksheets with the columns to search are named "SheetA" and "SheetB"

Assuming that your criteria is in column F on the sheet where this formula will be used


Copy the following formula down to each row needed:

=IF(OR(ISNUMBER(MATCH(F1,SheetA!A1:D1,0)),ISNUMBER(MATCH(F1,SheetB!A1:D1,0))),E1,"Not found")


The reference A1:D1 should be modified to suit the actual columns you have on each worksheet. To add additional sheets, simply extend the OR() condition with additional ISNUMBER(MATCH(...))


The formula is looking for a match for the value in F1 (for example) in the various sheets referenced. If a match is found (i.e. the ISNUMBER function returns true), the value in column E is returned.


Hope this helps.


Cheers,

Sajan.
 
Hi, I tried the above formula but it just returns "Not Found" for everything, my formula is as follows, have I done something wrong?

I dont actually need a different field returned, so have kept the same XDH1


IF(OR(ISNUMBER(MATCH(XDH1,BWApr!A12:AK13285,0)),ISNUMBER(MATCH(XDH1,BWMay!A12:AK13285,0)),ISNUMBER(MATCH(XDH1,BWJun!A12:AK13285,0))),XDH1,"Not found")


Thanks in advance
 
Luke M, My VB formula is;

[pre]
Code: 
Function BigLookup(xFind As Single, SearchRange As Range, xOutput As Range)
'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.Value
GoTo ValueFound
End If
Next c
Next sh
ValueFound:
'Hurrah!
End Function
[/pre]
The formula in the workbook is;


=BigLookup(XDH1,A2:AK2,XDH1)
 
Tarynmahon,

With respect to the IF(OR(ISNUMBER(MATCH(...))) formula you tried, I noticed that yor data ranges are not absolute. i.e. It will change as you copy the formula to additional rows. Could you try converting your ranges to absolutes

BWApr!$A$12:$AK$13285


I am assuming that you have manually verified a couple of the lookup values in xdh1 that they exist in the data range.


Hope that helps,

Sajan.
 
You must log in or register to reply here.
Back
Top