VLOOKUP - same value lookup but multiple rows with the value

**TCN** · 10-27-2011, 01:35 PM

Hi,
Please help.

attached please find a sample file with a data set and the totals sheet, here is what I need to do but can't find anywhere.

In Column H and I in the totals sheet, I need to take the lookup value in column A "LG ID#" and search the data set to produce the result that corresponds to the column name "TT" in both sheets.

The "x" in column "G" on the Data Set Sheet signifies that it is FRN and no "x" means it is Non-FRN.

What I want as an end result is for the numbers to auto pull for FRN and NON-FRN in the totals sheet from the data set.

Please help

**NBVC** · 10-27-2011, 01:44 PM

Perhaps try:

=SUMIFS('DATA SET'!$H$2:$H$25,'DATA SET'!$A$2:$A$25,$A2,'DATA SET'!$D$2:$D$25,$G2,'DATA SET'!$C$2:$C$25,"AP",'DATA SET'!$G$2:$G$25,"x")

and

=SUMIFS('DATA SET'!$H$2:$H$25,'DATA SET'!$A$2:$A$25,$A2,'DATA SET'!$D$2:$D$25,$G2,'DATA SET'!$C$2:$C$25,"AP",'DATA SET'!$G$2:$G$25,"<>x")

copied down

Note: It is recommended you don't use Merged cells, especially with numbers that you are going to manipulate.. it messes things up.

Also, some of your entries in the ST column have a trailing space... please clean them up.

**TCN** · 10-27-2011, 01:51 PM

Thanks NBVC unfortunately it didnt work.

I got the 1st values for D and W but the T and I didnt pull correctly and the other formula for Non-FRN didnt work at all.

**NBVC** · 10-27-2011, 01:57 PM

See attachment

Where is gone wrong?

**TCN** · 10-27-2011, 02:10 PM

My appologies NBVC,
It does work but needed some adapting and cleaning up of those cells you mentioned, even your form did it with my data for the last 2 in each column H and I, but it looks like I got it, going to work it into the document and will reply back when I know it works for sure.

Thanks again