Find cell value anywhere on sheet2 and return value on sheet 1

[gmfston]

Hello,

I am looking for help on a formula that will search for the dept (i.e. 10, 20) in row 1 and a GL account (i.e. N40010) in a column anywhere on the page on sheet 2 and populate the values on sheet 1, column e. The challenge is that the GL accounts on sheet 2 are not in the same columns and are followed with account descriptions (i.e. N40010.Medical Revenue-Self Pay). Thank you for your help!

 

[Khalid NGO]

Hi @gmfston Good day, and welcome to the forum.

I am not sure about your expected result, please give it a try:

=SUMIFS(IF(C5=10,'Sheet 2'!$M$5:$M$272,'Sheet 2'!$N$5:$N$272),'Sheet 2'!$H$5:$H$272,"*"&D5)

To be entered in Sheet1 E5, and copied down.

Regards,

 

[gmfston]

Hi @gmfston Good day, and welcome to the forum.

I am not sure about your expected result, please give it a try:

=SUMIFS(IF(C5=10,'Sheet 2'!$M$5:$M$272,'Sheet 2'!$N$5:$N$272),'Sheet 2'!$H$5:$H$272,"*"&D5)

To be entered in Sheet1 E5, and copied down.

Regards,

Thank you for your quick reply. The formula didn't work for all cells because the GL account codes are not all in column H. Some are in column d, e, f, g, & h. Also, it appears that some values are duplicating in sheet 1. For instance, the revenue amount of 5,644 from sheet 2, cell M10 is population in sheet 1 in cells E7 and E98 or Depts 10 and 20. Thank you again!

 

[gmfston]

I was studying your formula and I believe the formula needs to be modified to look for the GL code (i.e. N40010) and to search for the dept number in cells M1 and N1 in sheet 2. For instance, $5,644, cell M10, sheet 2 should be in cell E7, sheet 1. Amount $7,667, N61, sheet 2, should populate in E101, sheet 1. Hope this helps! Best, Gina

 

[bosco_yip]

Try,

E5, copied down :

=IFERROR(OFFSET('Sheet 2'!$L$7,AGGREGATE(14,6,ROW('Sheet 2'!$D$1:$D$265)/ISNUMBER(SEARCH(B5&" · ",'Sheet 2'!$D$8:$H$272)),1),LEFT(C5)),"")

Regards
Bosco

 

[gmfston]

Thank you Bosco. Very impressive . The only issue I found is that sheet 1, cell E26 is populating the total from sheet 2, M35 of 156,124.60 total 4002ng instead of M34, 4002ng adjustments other 2,936.96.

 

[bosco_yip]

Thank you Bosco. Very impressive . The only issue I found is that sheet 1, cell E26 is populating the total from sheet 2, M35 of 156,124.60 total 4002ng instead of M34, 4002ng adjustments other 2,936.96.

1] Good catch, try this revised formula with fixed by red color portion added,

in E5, copied down :

=IFERROR(OFFSET('Sheet 2'!$L$7,AGGREGATE(14,6,ROW('Sheet 2'!$D$1:$D$265)/ISNUMBER(SEARCH("#"&B5&" · ","#"&'Sheet 2'!$D$8:$H$272)),1),LEFT(C5)),"")

2] See revised attachment

Regards
Bosco

 

[gmfston]

Hello Bosco,

You are a genius! Once I changed the B5 to B6, the formula worked! I would love an explanation on the breakdown of this formula and if there are educational materials out there that would help me develop a formula similar to this. You are the best! Happy New Year!

 

[gmfston]

1] Good catch, try this revised formula with fixed by red color portion added,

in E5, copied down :

=IFERROR(OFFSET('Sheet 2'!$L$7,AGGREGATE(14,6,ROW('Sheet 2'!$D$1:$D$265)/ISNUMBER(SEARCH("#"&B5&" · ","#"&'Sheet 2'!$D$8:$H$272)),1),LEFT(C5)),"")

2] See revised attachment

Regards
Bosco

Hello Bosco,

I am trying to incorporate your formula into my actual spreadsheet, sheet 1, cell g80 and I can't get it to work. The correct values for the first 3 rows are in Column H for your reference. Can you assist? Best, Gina