Help with Index and Match

[pkohli]

Hi All,

I need to know, if it is possible to match the data in 2 different sheet and return the value?

I have name of OS in sheet 1 and details about their support in MS and IBM sheet.

I need to check is in A1 in sheet1 matches B2 in MS or IBM then return the value of 4th column.

It would be great help if you can reply back.

Thanks!

 

[Stormseed]

need to check is in A1 in sheet1 matches B2 in MS or IBM then return the value of 4th column

What if the lookup finds a match for A1 in both the sheets, namely, MS and IBM ?

 

[pkohli]

Can't be possible because data in both the sheets are different.

One sheet has microsoft OS info other has IBM os information.

 

[Jonmo1]

Is there any identifying factor on the Sheet1 to determine if it's found on the MS or IBM Sheet?

Like does the text in Sheet1A1 (or an adjescent column) contain MS or IBM?

 

[Stormseed]

Is there any identifying factor on the Sheet1 to determine if it's found on the MS or IBM Sheet?

I guess, the name of the OS in sheet 1 (column A) is the common factor in all of these sheets.

Like does the text in Sheet1A1 (or an adjescent column) contain MS or IBM?

A1 would contain the name of the OS like "Solaris", "Windows", "Unix" in range B:B of these sheets, namely, MS and IBM.

If this is the case, I do have a UDF, but this function would only return the first match it encounters across all worksheets:

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
                          Col_num as Integer, Optional Range_look as Boolean)

Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

	For Each wSheet In ActiveWorkbook.Worksheets
		With wSheet
		Set Tble_Array = .Range(Tble_Array.Address)
			vFound = WorksheetFunction.VLookup _
			(Look_Value, Tble_Array, _
			Col_num, Range_look)
		End With
		If Not IsEmpty(vFound) Then Exit For
	Next wSheet

	Set Tble_Array = Nothing
	VLOOKAllSheets = vFound
End Function

 

[pkohli]

It is like this:

In sheet 1 in column A, I have data

Windows Server 2003, Enterprise Edition (32-bit x86)
Windows Server 2003, Standard Edition (32-bit x86)
AIX 4.1
AIX 4.2

This data needs to be looked up in sheet MS & IBM column A, if it matches then it should return the value of column B of the respective sheet.

 

[Jonmo1]

Well, since it's only 2 sheets you want to look in, Suppose you could do it like this...

=VLOOKUP(A1,IF(ISNUMBER(MATCH(A1,MS!A:A,0)),MS!A:B,IBM!A:B),2,FALSE)

Hope this helps..

 

[Stormseed]

This data needs to be looked up in sheet MS & IBM column A

Are you sure it is column A ?

Earlier you said it was cell B2, here:

need to check is in A1 in sheet1 matches B2 in MS or IBM then return the value of 4th column

 

[Stormseed]

Pkohli,

You can also use the above posted UDF to your advantage in addition to Jonmo's resolution. Incase you want to lookup on multiple worksheets (more than two sheets) or should you have been looking forward for a VBA resolution.

the formula to use the UDF would be:

=VLOOKAllSheets("Windows Server 2003, Enterprise Edition (32-bit x86)",A1:B1000,2,FALSE)