Building HLookup and If formula

[j1ocelzn]

Looking for help to build a formula to return data in rows if one of it's cells contain an amount between two certain numbers. I think I need an HLookup on data on the first worksheet with an If statement if values are between $250-$500 for example. Easy enough?

 

[etaf]

we need to know a little more about the data layout and what you want to do

=IF( AND( Cell with values >= 250 , Cell with values <= 500 ) , Hlooklook(whatever you want to lookup) , "not between $250-$500")

 

[j1ocelzn]

Thanks! The first worksheet contains a contact list and donation amounts. I want the formula to return the contacts who have donated between a specific amount of money to the second worksheet. I have worksheets for each tier of donation. So the second worksheet will have the contacts who donated between $250-$500, the third worksheet will return all the contacts who donated between $500-$1000, etc. Would the "Cell with values" in the sample formula you wrote be the row with the donation amounts, and the Hlookup array would be the entire contact list, A3:L2000?

we need to know a little more about the data layout and what you want to do

=IF( AND( Cell with values >= 250 , Cell with values <= 500 ) , Hlooklook(whatever you want to lookup) , "not between $250-$500")

 

[etaf]

is it a list in column a of names and column b amounts

 

[j1ocelzn]

Yes, exactly!

is it a list in column a of names and column b amounts

 

[etaf]

i have setup a sheet , column A, B shows the name and contribution
column C is an IF to make the range for extraction - this is the only way i know how to do it, there probably is a better way

so it puts a 1 if the contribution is between 0 and 249
a 2 if between 250 and 499
and a 3 if 500 or more

than in columns F, G, H
I have extracted the names based on those ranges

these could be put into separate worksheets - but i thought I would show in one sheet for know , just to make sure its what you want

see next post

 

[etaf]

Sheet1

	A	B	C	D	E	F	G	H

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:84px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]contribution[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]range[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]0-249[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]250-499[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]>500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]fred[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]fred[/TD]
[TD="align: center"]harry[/TD]
[TD="align: center"]mike[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]****[/TD]
[TD="align: center"]john[/TD]
[TD="align: center"]linda[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]harry[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]joyce[/TD]
[TD="align: center"]pam[/TD]
[TD="align: center"]olive[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]john[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]jill[/TD]
[TD="align: center"]sara[/TD]
[TD="align: center"]liam[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]mike[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]linda[/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]joyce[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]jill[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]pam[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]sara[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: center"]olive[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: center"]liam[/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: center"]ryan[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF(AND(B2>=0,B2<250),1,IF(AND(B2>=250,B2<500),2,3)) F2 {=IFERROR(INDEX($A$1:$A$30,SMALL(IF(($C$1:$C$30)=1,MATCH(ROW($C$1:$C$30),ROW($C$1:$C$30))),ROW(A1))),"")} G2 {=IFERROR(INDEX($A$1:$A$30,SMALL(IF(($C$1:$C$30)=2,MATCH(ROW($C$1:$C$30),ROW($C$1:$C$30))),ROW(A1))),"")} H2 {=IFERROR(INDEX($A$1:$A$30,SMALL(IF(($C$1:$C$30)=3,MATCH(ROW($C$1:$C$30),ROW($C$1:$C$30))),ROW(A1))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[j1ocelzn]

I tried the F2 formula adapting it to return data to a second worksheet within the spreadsheet, but I clearly didn't do a good job. Essentially, what you have written is what I want with the addition of a Last Name field, and returning data to a second worksheet. What would that formula look like? Thanks!!!

 

[etaf]

just add the sheet name to the formula
{=IFERROR(INDEX(sheet1!$A$1:$A$30,SMALL(IF((sheet1!$C$1:$C$30)=1,MATCH(ROW(sheet1!$C$1:$C$30),ROW(sheet1!$C$1:$C$30))),ROW(A1))),"")}

then if we add a column for last name say B

{=IFERROR(INDEX(Sheet1!$A$1:$A$30&" "&Sheet1!$B$1:$B$30,SMALL(IF((Sheet1!$D$1:$D$30)=1,MATCH(ROW(Sheet1!$D$1:$D$30),ROW(Sheet1!$D$1:$D$30))),ROW(A1))),"")}

 

[j1ocelzn]

I get an error #NAME? when I entered this formula onto the second worksheet. The worksheet containing the data is named Master List. Where did I go wrong?

{=IFERROR(INDEX(Master List!$C$3:$C$2000,SMALL(IF((Master List!$L$3:$L$2000)=1,MATCH(ROW(Master List!$L$3:$L$2000),ROW(Master List!$L$L3:$L$2000))),ROW(C1))),””)}


[TABLE="width: 1331"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] B [/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]Donation[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Street Address[/TD]
[TD]Street Address 2[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip Code[/TD]
[TD]Email Address[/TD]
[TD]Phone Number[/TD]
[TD]Range[/TD]
[/TR]
[TR]
[TD] $ 100.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] $ 100.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] $ 120.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] $ 130.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] $ 150.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] $ 150.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] $ 250.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] $ 250.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] $ 300.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] $ 300.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] $ 500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] $ 500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] $ 500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] $ 500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] $ 500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] $ 525.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] $ 1,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]