VLOOKUP - One Criteria but having multiple table arrays

[mystic42]

Hi

I am trying unsuccessfully to do a Vlookup based on one lookup_value but looking into multiple columns on different worksheets.

The Lookup value is usually a text string concatenate several text and number cells together A1&B1&D1
125Apples55000 then find out if this string is present in sheet1 Column B or Sheet2 Column S ect.
If found in any column I want to return only the one result, if True 125Apples55000 or if False #N/A (or " not found")


If you have a better solution than Vlookup let me know, as I'm opened to suggestions

Any help or guidance would be greatly appreciated.


Cheers and Thans

Andrea

 

[DILIPandey]

Hi Andrea,

So you don't want to know in which sheet / which column it finally found ?


Regards,
DILIPandey

 

[mystic42]

Hi DILIPandey

It would have been good but I didn't want to complicate my request to much. Yes it would be good but it would be happy if I had a formula that did basically the trick.

Regards


Andrea

 

[DILIPandey]

Okay.. those other sheets have data like :-

125Apples55000

or
125 Apples 55000 all three in separate columns ?

Regards,
DILIPandey

 

[mystic42]

The concatenated string always has the same format in the various columns in separate sheets. However the exact same entry such as 125Apples55000 would be maybe only ins sheet2 column C but not in sheet1, it would be highly unlikely for there to be 125Apples55000 in sheet1 column B as well. I am trying to ensure that our internal accounts match those of external reports. Which is why I wanted to have the internal sheet checking to see if the corresponding cell was found in one of the external sheets within the same workbook.

Regards

Andrea

 

[DILIPandey]

However the exact same entry such as 125Apples55000 would be maybe only ins sheet2 column C

whole string "125Apples55000" or just a string part like 125 or Apples or 55000 would be there in column C ??


Regards,
DILIPandey

 

[mystic42]

Whole string 125Apples55000

 

[DILIPandey]

Okay, Use the function as "=exists(a1)" without quotes where a1 is string you wish to search in entire workbook but first copy this code in a module:-

Function exists(text)
'created by DILIPandey
Application.Volatile
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> ActiveSheet.Name Then
Set a = Sheets(Sheet.Name).Cells.Find(text)
If Not a Is Nothing Then
exists = a
End If
End If
Next
End Function

To create module-> press alt + F11 from excel window, press alt + i +m, paste this code.\

Regards,
DILIPandey

 

[mystic42]

Thanks DILIPandey

But in both Excel 2003 & 2010 in the cell that has the formula =exists(A1) it always returns the value 0 ,in both instances where the duplicate value of A1 is in another worksheet or not.

Regards

Andrea

 

[DILIPandey]

Okay... are you sure that value of a1 is there in other sheet i.e., it is exactly same ? Can you check using Exact function ?



Regards,
DILIPandey