Help with Index and Match

pkohli

Board Regular
Joined
Aug 2, 2005
Messages
71
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Can't be possible because data in both the sheets are different.

One sheet has microsoft OS info other has IBM os information.
 
Upvote 0
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?
 
Upvote 0
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:

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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..
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top