1. ## formula help

i have the attached file which explain my case, i need a formula that read a specif data from table2 & align it in table 1 for the corresponding name, highlighted in red.

TIA

dubdub

2. Put: =IF(I\$2=\$G3,\$E3,"") in I3 and fill across and down.

3. thanks Kweaver, but may be i did not describe the question enough what i am after is a formula that will match columns a&e in the first table with column h & the array i3:04 in the second table and if match exist it will pick up the right value from i2:02 and put it in column g3....

dubdub

4. I'm afraid I don't understand what you want done.

Do you want the year (in red) to be generated based on the location of a "1" in table 2?

5. The red column represent what the formula outcome supposed to be based on the match if exist and it would read from table2

6. Try

=INDEX(\$I\$2:\$O\$2,MATCH(\$E3,\$I3:\$O3,0))

7. my appology, the attached sexample has to do the match based in two columns in table 1, column a&e with table 2 columnm h for the name and the array i3:04 for count and if match exist it will put the year value from range i2:02 in column g.

8. That is what my formula does.

9. thanks Bob, but if you consider the example i attached lately, it works for the first name in table-1 because there is a match in table 2 but the next name it still give a value of 2014 although the names are not matched.the value supposed to be blank.

10. i still have hope....

11. How's this in G3, then fill down:

=IF(ISERROR(MATCH(D3,\$H\$3:\$H\$4,0)),"",SUMPRODUCT(( \$I\$2:\$O\$2)*INDIRECT("I"&(2+MATCH(D3,\$H\$3:\$H\$4,0))& ":O"&(2+MATCH(D3,\$H\$3:\$H\$4,0)))))

12. If the "count" could be other than a "1", change to this ARRAY formula:

=IF(ISERROR(MATCH(D3,\$H\$3:\$H\$7,0)),"",SUMPRODUCT(( \$I\$2:\$O\$2)*(0<(INDIRECT("I"&(2+MATCH(D3,\$H\$3:\$H\$7, 0))&":O"&(2+MATCH(D3,\$H\$3:\$H\$7,0)))))))

CTRL+Shift+Enter

See attached.

13. thanks Kweaver, i prefered using the second formula, because it covers the array value chnages, but i have two observations, the first one the g5 value is weird for unknown reason to me, all what i did i made the name the same.second thing as i indicated before it has to do match between two values in table 1& 2 and these are the count & the name, the current equation is based on name match. the count value in table 1 is variable. Pls. see attachment. thanks again for your patient with me.

14. You had some spaces or blanks in the 5th row in table 2. If you delete them, the calculation is correct: 2017.

15. i do not know what is happening i made all the data format identical to where it works but still no sucess,attched, any help in the the two parameters/values match between the two tables i mentioned in my previous post.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•