Excel Lookup Series #11: INDEX & MATCH functions Two 2 Way Lookup

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
I have spent now hours of trying to google this solution over the internet with no success. Currently I'm using macro code that writes formulas in the cells and generates results from the formulas themselves. Basic formula looks something like this:
=INDEX(zflex!AA:AA,MATCH(KHL_YM26!A:A,zflex!Z:Z,0))

Sheet KHL_YM26 being sheet A
Sheet zflex being sheet B

Up till now I been using macro codes like below
Code:

Sub Test()
'
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(zflex!C[-2],MATCH(KHL_YM26!C[-28],zflex!C[-3],0))"
    Range("AC2").Select
    Dim K6 As Long
        K6 = Cells(Rows.Count, "A").End(xlUp).Row
        Selection.AutoFill Destination:=Range("AC2:AC" & K6)
End Sub

This code is for one column. Now my problem is that I need to pull over data from 11 columns from sheet B to sheet A. So by my method so far I need the macro has to perform the similar task 11 times. Performing the lookup 11 times might have been ok a month ago but I have learned so much lately that I know that this approach is ridiculous. With proper VBA code, setting variables etc I bet its possible to do one lookup or match between these two sheets, get those info from those 11 cells and place them in corresponding cells on sheet A.

So the following is what I need.
MATCH lookup value is from sheet KHL_YM26 column 1
MATCH lookup array is from sheet zflex column 26


Now it should take the info from sheet zflex from following columns:
40 27 10 25 9 29 11 31 35 36 37

And place them to sheet KHL_YM26 columns in respective order:
20 29 30 31 34 35 36 37 38 39 40

Or if its visually better to understand it
zflex -----> KHL_YM26
40 -----> 20
27 -----> 29
10 -----> 30
25 -----> 31
N/A -----> N/A
N/A -----> N/A
9 -----> 34
29 -----> 35
11 -----> 36
31 -----> 37
35 -----> 38
36 -----> 39
37 -----> 40


It would be nice to know how to achieve this in an efficient way without writing them formulas in and performing unnecessary lookups. I need to do this with some more sheets it would be nice to learn how its properly done. Doing like 20 lookups in total from 3 sheets is not efficient at all.

I have attached xls with real data. Both sheets have header rows.



Any help would be extremely appreciated.
Cheers
Rain

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.)

Hi,

I know the LOOKUP function in Excel is provided for backward compatibility. But isn't it a lot easier to use this function for left lookups instead of the Index/Match combination?

See attached sheet. Just wondering ... Does LOOKUP still exist in XL 2007?

cheers

Teylyn

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

I think the title pretty much sums this one up.. I highlighted a word in a sentence in a cell and was wandering if there is a way to transfer that formatting to my index or lookup cell. PLEASE SEE ATTACHED..

Thanks for the help!