need help with vlookup or macro

[ncexcel]

i need to search if value in excel sheet B exists between values in excel sheet A between start and end


excel sheet A

START	END	HBW	LBW
0	999	22431	17945
1000	1999	22395	17916

<tbody>
</tbody>

Sheet B

500

Since 500 exists between 0 and 999, i want 22431 returned. how can i accomplish this?

 

[platonic567]

Assuming 500 is in cell A1 of Sheet B, use below formula in B1:

=SUMPRODUCT(FREQUENCY(A1,'Sheet A'!$B$2:$B$3),'Sheet A'!$C$2:$C$4)

 

[ncexcel]

That didn't work. I am not looking to see how many times the value occurs. i am looking if a value is between two columns then get the corresponding value from that row for a different column

 

[platonic567]

Is your Sheet A setup like below with the name as "Sheet A"?

	A	B	C	D
1	START	END	HBW	LBW
2	0	999	22431	17945
3	1000	1999	22395	17916

<TBODY>
</TBODY>

Can you perhaps give more details on what happened when you entered the formula?

 

[ncexcel]

yes i pasted two rows there are 32 rows


=SUMPRODUCT(FREQUENCY(A2,SheetA!A$2:A$32),SheetA!$B$2:$B$32)

#VALUE! is what the formula returned

 

[platonic567]

Change your formula to below and see:

=SUMPRODUCT(FREQUENCY(A2,SheetA!A$2:A$32),SheetA!$B$2:$B$33)

 

[ncexcel]

now i get values but they are incorrect . for example

in SheetA has 32 rows and sheetB, i have 18000 rows.

SheetA

Loop Length START	END	Highest BW	Lowest BW
0	999	22431	17945
1000	1999	22395	17916
2000	2999	21623	17298
3000	3999	21141	16913
4000	4999	20248	16198
5000	5999	18926	15141
6000	6999	18231	14585
7000	7999	16807	13446
8000	8999	15454	12363
9000	9999	14297	11438
10000	10999	13101	10481
11000	11999	11655	9324
12000	12999	10145	8116
13000	13999	8938	7150
14000	14999	7803	6242
15000	15999	6539	5231
16000	16999	5811	4649
17000	17999	4952	3962
18000	18999	4172	3338
19000	19999	3559	2847
20000	20999	2943	2354
21000	21999	2499	1999
22000	22999	2058	1646
23000	23999	1735	1388
24000	24999	1426	1141
25000	25999	1132	906
26000	26999	906	725
27000	27999	709	567
28000	28999	501	401
29000	29999	213	170
30000	100000	127	102

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

sheet B

Sheet B
0 populates 999 instead of 22431
20 populates 1999 (should be 999 and 22431)

 

[ncexcel]

nevermind its fixed. just readjusted the columns and looks right

 

[platonic567]

nevermind its fixed. just readjusted the columns and looks right

Well, I guess you're welcome then.

 

[ncexcel]

oh thank you thank you.. too excited about getting this worked out. although i cant understand how the formula works