Formula to extract value from 2 rows below.

**theocharis** · 09-14-2020, 05:58 PM

Hello to everone in the forum.

I would like to kindly ask for the expertise and help of someone regarding an important project that I work on in my company. The problem that I am facing is the following: Imagine that I have a dataset similar to sheet1 to the workbook that I attached. I am always receiving bond transactions for the same company always twice indicating one time "Sell" and one time "Buy". The dataset is large with each week around 300 observations coming out. What I need to do is to build a formula so that ONLY ONE of the two cases for each deal either "Sell" or buy but NOT both of them are copied in sheet1 (imagine that it is similar to what I have attached). This means that the formula needs to include only one row of the two that refer to the same company, omit the next one, and move for the next company where it does the same etc. In sheet 2 I also need to extract not the name but the category of the company that issues the bond, which I specify in columns K & L. The formula that I have written in Sheet 2 which apparently doesn't work is the following:

=if(Sheet1!D2="Sell";Vlookup(Sheet1!C2;Sheet1!$K$12:$L$16;2;False);Vlookup(Indirect(Address(Row(Sheet1!C2)+1;Column(Sheet1!C2));Sheet1!$K$12:$L$16;2;False)).

Effectively my intuition is the following: If the cell in column D is "Sell" then go to the table and find me the category, otherwise if the cell in column D is buy, then ommit that row, go to the next one and again take the value from the table specified in columns K and L.

I really struggle to find out what I am doing wrong. Can someone please help me with that one? It would be much appreciated.

Thanks in advance,

Theocharis

**José Augusto** · 09-14-2020, 06:52 PM

Try this using a helper column (to simplify)

In Sheet2!L2 use the following formula and drag down

Formula:

Please Login or Register  to view this content.

To get the table use in sheet2!A2 and drag down and forward the following formula

Formula:

Please Login or Register  to view this content.

See the file for clarification

**theocharis** · 09-14-2020, 07:03 PM

Jose Augusto thank you so much!!!! This is amazing feedback. God bless you!!!

**theocharis** · 09-14-2020, 09:29 PM

Jose Augusto,

I just understood how the formula works but I still have one question. On sheet 2 column A i do not want the name of the company but rather its category which I specified in sheet1 on the table. How is it possible to exctract that value instead of the name in the formula?

**wk9128** · 09-14-2020, 11:02 PM

Is it like this?

worksheet sheet2 , A2 cell array formula , Drag down

HTML Code:

worksheet sheet2 , B2 cell array formula, Drag down and accross

HTML Code:

**bebo021999** · 09-14-2020, 11:54 PM

Alternative non - array solution
A2:

Please Login or Register  to view this content.

B2:

Please Login or Register  to view this content.

Copy B2 to H2

Drag all down

**theocharis** · 09-15-2020, 12:11 AM

Thank you very much. The indexed one works perfectly fine, for the non-index when i include a new data and paste the formula below in sheet A2 it leaves the row blank and does not make any changes.

**theocharis** · 09-15-2020, 03:59 AM

both formulas work perfectly!!!!! thank you so much... just one question though for my personal understanding. I do not understand the purpose of the iferror in the beginning. So the formula is looking if there is an error at which exactly cell? What is the overall intuition of this function for the specific purpose? Once again, it works perfectly fine i just want to understand it more in depth that is why i ask

**bebo021999** · 09-15-2020, 04:11 AM

IFERROR(FORMULA,"") gives blank instead of error sign (#N/A, #VALUE"...)
Try to remover leading "IFFERROR(" and traling ","")" to see what happens without IFERROR

**theocharis** · 09-15-2020, 04:14 AM

Oh yeah i got it completely understood now. Thanks once again.

All the best!!

**theocharis** · 09-15-2020, 05:28 AM

Dear Bebo,

while this formula works perfectly for what I asked i am trying it right now for something kinda different and it does not give me the value that I want. As a further step what I need is in the column M of sheet 2 to specify a new variable called PnL. In this column what I need to do is from sheet 1 is the following: From the G column of sheet 1 I need to substract the value of buy from the value of sell and then multiply with the value in Column C. so for instance for the first once i need to do: (101.2-101.9)*100,000. Then I need to do again the same but not between rows 3 and 4 but in rows 4 and 5. I need to make the calculation for two rows and then omit the second of this rows and go directly to make the calculation for rows 4 and 5 and multiply with the value of the corresponding cell in column c. Effectively, for the second calculation that would be: (102.6-103.1)*50.000. Could you please help me with that?

I know what you already provided me with amazing formulas which I understood and will be able to implement in the future. However, it is the first time that I am working with such a case that I need to omit rows and thus I struggle.

For intance column G in sheet 1 would be the following (perfectly alligned with the other variables that are already there)

Column G
G2 = 101.2
G3 = 101.9
G4 = 102.6
G5 = 103.1
G6 = 104.2
G7 = 105.6
G8 = 100.4
G9 = 103.6

Thanks in advance!!!

**theocharis** · 09-15-2020, 05:35 AM

I deleted the bid and ask columns and replaced them with the column G that I specified above*

Formula to extract value from 2 rows below.

LinkBack

Thread Tools

Rate This Thread

Display

Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Re: Formula to extract value from 2 rows below.

Thread Information

Users Browsing this Thread

Similar Threads

Formula to extract unique values/distinct values in rows in excel

[SOLVED] Need a formula to extract name of fathers and place them in the same rows with mothers

[SOLVED] Formula to extract certain rows (based on a single criteria) for a dashboard

Formula to extract data from rows in two worksheets

formula to extract multiple rows based on reference

[SOLVED] Formula to extract Rows and Columns headers to put inside a cross table

[SOLVED] Use a formula within a macro to extract data and copy down x number of rows

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions