How to find value in another table and return value from same row

Hi all

This is my first post here so apolgies if I'm using the wrong sub-forum or anything like that.

I really need some help with the following problem in Excel 2010.

I have two vaery large tables of data:

Table 1 is a long list of unique items in no particular order, each of which belongs to a unique group of items, for example:

Items Group ID
apple
pear
grape
fig
banana
guava
peach
mango

Table 2 lists all the Group ID numbers and in the same row as each Group ID number, shows the names of all the items belonging to that group, one item in each column. Different Groups have different numbers of items in them. For example:

Group ID Item1 Item2 Item3 etc.
214 apple pear grape
33 fig
7 banana guava
59 peach mango

Is there a formula I can use in the second column of Table 1 to look for each item in Table 2 and tell me what group it is in? For example for "apple" or "pear" or "grape" it would look in Table 2 and return the Group ID number "214".

Hope I've explained that clearly and thanks in advance for any ideas.

jpearson01,

Welcome to the forum!
Attached is an example workbook based on the criteria you described.
Row 1 is used to show which table is which
Row 2 is a header row so actual data starts in row 3
Table 1 is in columns A and B
Table 2 is in columns D:G

In cell B3 and copied down is this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how they get surrounded by the curly braces {}, do not try to add those yourself.

Please Login or Register  to view this content.

Also note that the Index range starts at D1. When doing a formula like this, I find it easiest to always have the index range start at row 1 regardless of where it actually is in the spreadsheet. Then ending row still ends where the actual data ends, in this case row 6.

You can also use UDF to reach your goal

Please Login or Register  to view this content.

Copy this code into VBE module

1. Press Alt+F11
2. Go toInsert > Module
3. Copy and Paste code into module
4. paste formula =MMATCH($A2,$F$2:$H$5,$E$2:$E$5) next to lookup values

attachement avaliable

Can you recommend a good online explanation of / tutorial on array formulas (I've never come across those curly brackets and Ctrl+Shift+Enter before)?

http://www.cpearson.com/excel/arrayformulas.aspx