Hi, Suppose I have data table in column A1 to A50 ( e.g. say here 1 to 50 numbers ). Now I want the result from B2 and down upto B10. Suppose in B1 I have number 38. From B2 and down I want 39, 40, 41 etc. actual result. I want a formula in cell B2 and below cells that it lookup at B1 cell, search that particular number in given table at A1 to A50 and show result in B2 and down.
Thanks
If your numbers are sequential (in order), why not just use =B1+1?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks FDibbins for quick reply.
No, given data in table are not just sequential numbers, and also sometimes in B1 it could be 38 or it could be 12. I want a formula that search number or data 12 or 38 from that table and show next number below in B2 onward.
I tried Index Match, Lookups but could not succeed.
Last edited by watermasa; 08-02-2019 at 09:18 PM.
Index/Match should work for you. You just need to include a row increment added onto the MATCH function
Thanks for suggestion. I tried some basics but fail. Could you please provide formula on it ?
Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
Attachment 635581
sending sample workbook. pl check.
Put this in C8 and copy down to C12. Then copy down from C13 to C17...
=INDEX(A:A,MATCH($C$12,A:A,0)+ROWS(A$1:A1)-5)
v A B C 1 2540 2 2520 3 2500 4 2480 5 2460 6 2440 7 2420 8 2400 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)-5,0) 2280 9 2380 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)-4,0) 2260 10 2360 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)-3,0) 2240 11 2340 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)-2,0) 2200 12 2320 2400 2180 13 2300 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0),0) 2160 14 2280 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)+1,0) 2140 15 2260 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)+2,0) 2120 16 2240 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)+3,0) 2100 17 2220 =OFFSET($A$1,MATCH($B$12,$A$1:$A$39,0)+4,0) 2080 18 2200 19 2180 20 2160 21 2140 22 2120 23 2100 24 2080 25 2060 26 2040 27 2020 28 2000 29 1980 30 1960 31 1940 32 1920 33 1900 34 1880 35 1860 36 1840 37 1820 38 1800 39 1780
Last edited by alansidman; 08-03-2019 at 01:35 AM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks FDibbins, I use above Index Match function after making some needs according to me.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Happy to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks