Two-Way Lookup, NOT Using Matrix

tjthom

New Member
Joined
Feb 8, 2016
Messages
3
Hello!

I am trying to use a formula to first Search vertically within a column for "VALUE A", then search within the row of "VALUE A" for "VALUE B", then output "VALUE C" from row 1 of the column that "VALUE B" is located within. Here is a photo of what I would like:



I have found something similar to this, but it requires that the lookup value be the top row of the reference/array. In my situation, the output will always be above the second lookup value (value B). Here is an article that describes something similar, but it is NOT WHAT I WANT: How to Use INDEX MATCH MATCH

Does anyone know of a formula that I could assemble that would search above the second criteria as shown above?

ADDITIONALLY... I would like to create a second formula that will search for the last "value B" in a row and return the resulting time of that as well. Essentially, the first formula I need is described in the picture above, but I would like to create a second, separate formula that gives me the latest time. Value B represents an assigned task someone will be doing, and I want to output the start and end time of that task, for that person.


Thank you in advance for any help you can provide! I'll take suggestions even if they are not full answers/solutions!!!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,239
Here's one way to do it.

Excel 2010
ABCDEFGHIJKLMNOP
1Value A7:007:157:458:008:158:308:459:009:159:30
2Alumni Ambassador 2
3
4Value B
54.03
6
7First Time
88:45:00 AM
9
10Last Time
119:30:00 AM
12
13Alumni Ambassador 1
14Alumni Ambassador 24.034.034.034.03
15Alumni Ambassador 3
16Alumni Ambassador 4
17Alumni Ambassador 5123456789
18Alumni Ambassador 6
19Alumni Ambassador 7abcabcaad
20Alumni Ambassador 8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
A8=INDEX(G1:AA1,MATCH(A5,INDEX(G1:AA20,MATCH(A2,C:C,0),0),0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
A11{=INDEX(G1:AA1,MATCH(2,1/(INDEX(G1:AA20,MATCH(A2,C:C,0),0)=A5)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



You'll want to put these in your preferred locations, and you'll need to adjust the ranges in them to match your sheet. The "Last Time" formula is an array formula, so confirm it with Control-Shift-Enter. Let me know how they work for you.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,617
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top