Index aggregate or similar formula to return data from another sheet

the footbal coach

New Member
Joined
Aug 16, 2015
Messages
10
Looking for a proper formula to find data in another sheet and return adjacent column. Using excel 2016 and 365 at times, but I would like this to work on as many versions as possible.

I need to look up a cell from another sheet and return a value and its adjacent value (if possible, if I need two formulas, I understand). I only have 250 rows or so.

The second picture is Call sheet, I would like to look the data from B1 (jet sweep lt) from the master sheet (picture 1) and return those values back to the call sheet like in the example. Where the formula finds "jet sweep lt" in the master sheet and returns "A1" and "JET SWEEP LT" in the first and second column. (If I had put "JET" in B1, it would have returned A1,A2,A3,A4,A6,A7 etc. down the column.) I have done this with Vlookup, but I could have matching data that needs to return separate row data.

I have seen index match could work, maybe Aggregate to count multiple matches, but I am very out of my element with those functions. I just need to be able to find numbers or words.


1613353815710.png
1613353914169.png




This is an example of what my finished sheet looks like currently, but I have recently had trouble with matching data I did not anticipate before.

1613354433106.png
 

Attachments

  • 1613353841559.png
    1613353841559.png
    10.9 KB · Views: 0
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
Here is one approach using the INDEX/AGGREGATE approach...
Cell Formulas
RangeFormula
A2:A20A2=IFERROR(INDEX(Master!$A$2:$B$21,AGGREGATE(15,6,(ROW(Master!$B$2:$B$21)-ROW('Call Sheet'!$B$1))/ISNUMBER(SEARCH('Call Sheet'!$B$1,Master!$B$2:$B$21)),ROWS(A$2:A2)),1),"")
B2:B20B2=IFERROR(INDEX(Master!$A$2:$B$21,AGGREGATE(15,6,(ROW(Master!$B$2:$B$21)-ROW('Call Sheet'!$B$1))/ISNUMBER(SEARCH('Call Sheet'!$B$1,Master!$B$2:$B$21)),ROWS(B$2:B2)),2),"")

MrExcel20210214.xlsx
AB
1
2A1Jet Sweep LT
3A2Let LT-Power 33
4A3Jet LT-24 OT
5A4Jet LT-Power 33 Pass
6A5Power 47-O
7A6Jet LT-31 Trap
8A7Jet LT-35 G
9A8Jet LT-24 Counter
10A9Jet LT-35 G Pass
11A1035 Down
12A11Jet LT-35 G Pass Switch
13A12Power 33
14A13Jet LT-35 G Pass Y-Cross
15A14Jet Sweep RT
16A15Jet RT-Power 34
17A16Jet RT-43 OT
18A17Jet RT-Power 34 Pass
19A18Power 28-O
20A19Jet RT-32 Trap
21A20Jet RT-36 G
Master
 
Solution

the footbal coach

New Member
Joined
Aug 16, 2015
Messages
10
Thank You very much. I think Its working perfectly. My conditional formatting is messing up, but this has been a life saver. Thank you again
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
Glad to help. Post back if you run into issues with the conditional formatting. It looks as if you might want to fill cells depending on the first letter of the play's index code...with "A" filled red with white font, "C" filled blue with white font, etc. If that is true, then conditional formatting based on a formula should work, =LEFT(cell reference,1)...as shown in this example.
Cell Formulas
RangeFormula
A2:A20A2=IFERROR(INDEX(Master!$A$2:$B$25,AGGREGATE(15,6,(ROW(Master!$B$2:$B$25)-ROW('Call Sheet'!$B$1))/ISNUMBER(SEARCH('Call Sheet'!$B$1,Master!$B$2:$B$25)),ROWS(A$2:A2)),1),"")
B2:B20B2=IFERROR(INDEX(Master!$A$2:$B$25,AGGREGATE(15,6,(ROW(Master!$B$2:$B$25)-ROW('Call Sheet'!$B$1))/ISNUMBER(SEARCH('Call Sheet'!$B$1,Master!$B$2:$B$25)),ROWS(B$2:B2)),2),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A20Expression=LEFT(A2,1)="C"textNO
A2:A20Expression=LEFT(A2,1)="A"textNO
 

the footbal coach

New Member
Joined
Aug 16, 2015
Messages
10
I think everything has worked out great. The formula was making all my conditional formatting selections delete, just fixed those and everything is great. thank you again
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
Good to hear...thanks for the update.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,920
Messages
5,627,627
Members
416,257
Latest member
salomon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top