Matching two cells returning a value in third cell

Question

Level 1

0 points

Matching two cells returning a value in third cell

Hi All,

I am having an extremely hard time with this and hope that I ask the question properly.

Table 1 for arguments sake is....

Train	Train Time
2000
2002

Table 2 in a separate sheet for arguments sake is....

Train	Setup Time	Trip Duration	Total Train Time
2000	10	55	65
2002	15	60	75
2004	20	65	85

So what I am trying to do is in Table 1 Train Time Cell I want to match Train 2000 in Table 1(say cell A1) to Train 2000 in Table 2 using the range of all the cells(A1 through A3). If there is a match between the tables in separate sheets it would return Total Train Time In Table 2 and put that value in Train Time in Table 1. I am dealing with 100's of cells in each table so that is why this is so important to me.

I hope this is not too confusing or I am missing something so easy.

I tried a If Exact function using a cell in table 1 (A1) to cells in table 2 (A1 through A3) and it will return a time value if and only if its A1 in sheet one table 1 matching A1 in sheet 2. If I am to try matching 2002 it will return a false. It is almost like it does not recognize trying to match between a range of cells.

Thank you in advance

Bill

MacBook Pro (Retina, 13-inch,Early 2015), OS X Yosemite (10.10.5), Numbers App

Posted on Oct 10, 2015 8:25 PM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,383 points

Oct 11, 2015 6:23 PM in response to bdawson266

Hi Bill,

A combination of INDEX and MATCH is a powerful and flexible lookup function.

Formula in B2 of Table 1 (and Fill Down)

=INDEX('Table 1-2'::D,MATCH(A2,'Table 1-2'::A,0))

My cheat sheet for INDEX MATCH is:

=INDEX(Column I want a return value from,MATCH(My Lookup Value,Column I want to Lookup against,Enter “0″ ))

Regards,

Ian.

Edit: Tables are Table 1-1 and Table 1-2 (renamed when I pasted your tables into Numbers). Ian.

Reply

Answer 2

Badunit

Level 7

25,802 points

Oct 11, 2015 5:51 PM in response to bdawson266

Because there are usually multiple ways to do things, I'll mention some other ways to do the same.

Index & Match are the "power user" way of doing lookups. The two functions work great together and are very flexible and, all-in-all, using them is probably the best way to do what you want to do. But there are also lookup functions built into Numbers. LOOKUP is one of them. Using the same table names as Yellowbox used, the equivalent formula for cell B2 using LOOKUP would be:

=LOOKUP(A2, 'Table 1-2'::A, 'Table 1-2'::D)

Note that if there is no exact match, a "close match" will be made instead. So be sure you have your data table complete so all you'll ever get are exact matches.

Another lookup function is VLOOKUP. With it, you can specify exact match.

Reply

Answer 3

bdawson266 Author

Level 1

0 points

Oct 11, 2015 6:36 PM in response to Yellowbox

Ian thank you so much...That worked right away. I am stoked. To continue with this I have separate tables for each group of trains that make certain trips that would make the trip duration different.

Taking the formula that you did **=INDEX(Column I want a return value from,MATCH(My Lookup Value,Column I want to Lookup against,Enter “0″ ))**

Can I in the same cell that I put that formula in add to it? Meaning look for more trains with a different trip duration?

Reply