Excel Lookup Series #12: INDEX & MATCH functions Example 2!

I wonder if someone could kindly help me how to use the INDEX/MATCH lookup function.

My workbook "Requisition Form" has a table (which includes headers) in Sheet1!$A19$A:$L19$L. Column C is headered "Product Code". I need this to match with Column A in a Sheet1 of "X:\PRODUCTS LIST - DO NOT MODIFY!.xlsx" and pull through the corresponding data in Column F. This data needs to be returned into Column D of the Requisition Form.

Sorry I am not good at explaining.... hopefully someone can help!

Many thanks

I am trying to make a formula that will look up peoples names and their relevant info. They are sorted into groups on sheet1 ,Named group 1 , group 2 ect. On sheet 2 when I enter the group name in cell C4 I need to have a formula list up the peoples relevant info like if they are male or female, Birthday and a few others as well.

Then on sheet3, I only want to enter a single persons name and have their data be brought up.

I have simplified the workbook, as there is a lot more info to enter other than birthdays ect, plus there are thousands of peoples data that has to be entered, so I need to be able to expand the lists ect as the list of people grow.

TIA.

Hi,

I don't know whether this requirement has already been resolved, i am posting without really having searched thoroughly in this forum.

I need to auto-populate data (in the yellow-colored cells as in the output screenshot) pertaining to certain months from a Source sheet (refer source screenshot) when month is changed in a cell inorder to populate certain graphs.

I've also attached a sample workbook with dummy data. Can someone help??


Sarang

Hi all,

Can anyone help with the following task please.
I need to find a way for excel to auto return and fill the 'Reports To' column, as it is 1000's of rows long and I currently have to do it manualy.

I have changed the names to dumies obviously but the concept is the same. It is company hierachies.

2 3 4 5 6 7 8 9 10 Reports To Mickey Mouse Mickey Mouse Donald Duck Mickey Mouse Mini Mouse Donald Duck Roadrunner Mini Mouse Bugs Bunny Roadrunner Kermit Frog Bugs Bunny Elmo Bugs Bunny David Beckam Bugs Bunny Kermit Frog Bugs Bunny

Please provide a function that will allow me to combine two large volume of data into one spreadsheet and line up each item (text). I'm looking for the fastest and easiest way to creat a matching line item across columns for up to 800 names. Note, I have tried both VLOOKUP & MATCH functions but neither returned the row/column that the matching item was in. instead it returned a numeral, such as (1, 4, etc.)

I'm at my wits end trying to figure out what is wrong! I'm new at excel vba and new at forums so please have patience!

I keep receiving Run Time Error "Could Not Set The Value Property:Type Mismatch" in the last line of code. My Input values show up fine but it seems the Index is not looking up the values.

This works fine in the worksheet:
=(INDEX(B2:M53,MATCH(Q17,B1:M1,0),MATCH(Q18,A2:A53,0)))

Private Sub txtUiMulti_Exit(ByVal cancel As MSForms.ReturnBoolean)
'Returns United Inch multiplier from UI Table
Dim Input1 As Long
Dim Input2 As Integer
Dim ws As Worksheet
Set ws = Worksheets("UnitedInch")
Worksheets("UnitedInch").Activate
'Gets the frame width from userform, compares to top row of table
Input1 = txtFrameWidth1.Value
'Gets total united inches from userform, compares to 1st column of table
Input2 = txtUnitedInchTotal.Value
'Should return a value from A2:M53
txtUiMulti.Value = Application.index(("B2:M53"), Application.Match(Input1, ("B1:M1"), 0), Application.Match(Input2, ("A2:A53"), 0))
End Sub

Here is a sample of the table it runs 53 rows:
0.5 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 12 3.00 3.00 3.67 4.00 4.00 4.67 5.00 5.00 5.67 6.00 6.00 6.67 14 3.00 3.67 4.00 4.00 4.67 5.00 5.00 5.67 6.00 6.00 6.67 7.00 16 3.67 4.00 4.00 4.67 5.00 5.00 5.67 6.00 6.00 6.67 7.00 7.00 18 4.00 4.00 4.67 5.00 5.00 5.67 6.00 6.00 6.67 7.00 7.00 7.67 20 4.00 4.67 5.00 5.00 5.67 6.00 6.00 6.67 7.00 7.00 7.67 8.00 22 4.67 5.00 5.00 5.67 6.00 6.00 6.67 7.00 7.00 7.67 8.00 8.00

hi all, not posted on here for some time but have a small issue I am trying to deal with

I am trying to create a function we

if the values in columns A & B (sheet 1) are the same as the values columns A & B (sheet 2) return MATCH, if not return NO MATCH

hope you can help, thanks in advance

Phil

I have two colums of data. Column A1:A1000 contains either a 1 or 0 (1 for true and 0 for false). Column B1:B1000 has last names.

I am trying to create a list of the names in column B with a corresponding 1 in column A.

In theory, I guess I want to find the first 1 in column A and capture the name associated with it in Column B, then do another search only using the data after the first occurance of 1 in column A.

I want to start my list of names in cell C1 and the next in C2, etc...

Thanks for any help provided.

John

Good Morning All,

I have read some peoples suggestions as to how to lookup a value based on
two or more criteria using a combination of INDEX and MATCH fuctions. I have
tried to repicate this myself however my fuctions always return a #NA error.
Below is the data and function as it appears normally and under that is the
same range, showing the formula. I am try to match a model and a serial and
return the result (WO or R). I can't see why this formula isn't working, as
far as I've seen its should work on a combination of letters and numbers. If
anyone can see how to correct this could you please let me know.

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509
=INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
AOT24RZAL T005294 R AOT24RZAL T006183
=INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
AOT24RZAL T007509 WO AOT24RZAL
T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))