Formula to extract values

Hey, so what I want to do, is to make a formula in the cells marked with yellow that can find the values posted in sheet2 (The values in sheet 2 will be hard coded). For example, “items” (Test1, test1 etc.) in column B has a corresponding value in column F and for Column C it is corresponding with column G. I want to be able to get the values in sheet2 to sheet1 and be able to them in sheet 1 without gaps between the values (Blank rows).

TL;DR want to make a formula in sheet1 to get the values in yellow background(Which are from sheet2)

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Originally Posted by Pete_UK

Put this formula in D3 of sheet Ark2:

Formula:

Please Login or Register  to view this content.

and copy it down as far as you need to (can be beyond the lists that you have in columns B and C). |Then put this formula in C3 of the sheet Ark1:

Formula:

Please Login or Register  to view this content.

then copy this down as far as you need to.

Then change the value in C2 and see the numbers change accordingly.

I've set this up in the attached file, so you don't have to worry about translating the function names.

Hope this helps.

Pete

Thanks, this does work! But let say that I have 20 test values (Test1, test2,... test20). Could I just make 20 hidden columns in sheet2 with formulas:

Formula:

Please Login or Register  to view this content.

And just change the cell reference?

And in sheet1, could the formula be independent for each test value. So that I can have 20 different test values and each column have different values in them?

Anyway, thank you for helping me!


Edit*

I got it working the way I wanted. Thank you!!