# need help with vlookup or macro

#### ncexcel

##### New Member
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?

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Assuming 500 is in cell A1 of Sheet B, use below formula in B1:

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

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

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?

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

Change your formula to below and see:

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

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)

nevermind its fixed. just readjusted the columns and looks right

nevermind its fixed. just readjusted the columns and looks right

Well, I guess you're welcome then.

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

Replies
8
Views
159
Replies
0
Views
116
Replies
1
Views
122
Replies
20
Views
594
Replies
1
Views
298

1,221,241
Messages
6,158,733
Members
451,513
Latest member
EbenAgya

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back