If with nested vlookup

[John H]

Hi,


first time poster but long time admirer of the excellent information I have found on here so thank you all initially. Now hopefully one of you very clever people can help me.


The formula above isn't a problem, I can do this quite easily however I would like the when a result is true for it to be added in the next available row.


for example, I have data that covers A1:E15, I only need two piece of info from the data which is in column A and Column E, but only if it meets certain criteria. Now on another worksheet I have the if with nested vlookup formula, in A1:B15, This is so i can cover the whole data set in the other sheets range. Now when I apply the formula I do get the correct results. However if the correct results are in row 6, 10, 15 then on my target sheet they appear at row 6, 10 ,15 on it.


I want to know if it is possible (or if there is anyway short of VBA) of doing this so the correct results will appear in row 1,2,3 etc regardles of their position in the raw data range?


Any help will be much appreciated.


Thanks All.

 

[SirJB7]

Hi, John H!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


I must say I'm afraid I didn't fully understand the question, could you please elaborate a bit more, post the involved formulas, write down manually expected output, or even better upload a sample file? Refer to second green sticky post for uploading guidelines.


Regards!

 

[John H]

Hi SirJB7,


Thank you so much for your guidance.


I have searched but unfortunately I cant find what I am looking for. Maybe because I am struggling to define it. I'll try again below for you.


On sheet 1 I have data in the range A2:E15, Column headers are Ref Code in A1, Value B1, End date C1, Theshold D1, Over threshold E1.


The data in Column A and Column E is what I need to extract if it meets certain criteria.


In another table on sheet 2 I have a column headed RefeCode in A1, Over Threshold In B1. I have the following formula =if(E2="Yes",vlookup(A2,sheet1!A2:E15!,1,0),"") in cell A2 copied down to A15. In cell B2 I have a vlookup formula that returns the value of A2 column 3 (End Date). this is also copied down to B15.


Now if the results are true I only get them returned in the corresponding rows. i.e. on sheet 1 rows 4 and 7 have yes's in column e. On sheet two these would be represented in rows 4 and 7.


I want to be able to have the results appear in sheet two in rows 2 and 3 and so on regardless of the row the data is in on sheet one.


In this example the data in row the data in row 4 sheet 1 would appear in row 2 sheet 2 and the data in row 7 of sheet 1 would appear in row 3 sheet 2.


I hope this is clearer?


Apologies I am still trying to figure out how to attach a file it wouldn't work for me before.


Thanks again for your help.

 

[SirJB7]

Hi, John H!

While I read your detailed explanation, you might want to read this uploading link:

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!