Vlookup (or range lookup) to consolidate results

[bmpreston]

Looking to pull a range of a VLOOKUP if a value exists.

Pull this to a dynamic report from multiple sheets onto one consolidated sheet.

Please see the example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]value to match[/TD]
[/TR]
[TR]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]bar[/TD]
[TD]bar[/TD]
[TD]bar[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD]helloworld[/TD]
[TD]helloworld[/TD]
[TD]helloworld[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
John Snow <--worksheet name

[TABLE="width: 500"]
<tbody>[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]value to match[/TD]
[/TR]
[TR]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD]something[/TD]
[TD]something[/TD]
[TD]something[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]more[/TD]
[TD]more[/TD]
[TD]more[/TD]
[TD]-2[/TD]
[/TR]
</tbody>[/TABLE]
Elon Musk <--worksheet name


This is two example sheets of 12 in a workbook.

I need to put the name of the worksheet in a cell; add a few rows for gaps; then bring over the rows with a negative number; few rows, then the new worksheet name; then that sheet's negative values.


The Results would look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]John Snow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]foo[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]foobar1[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elon Musk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]anothervalue[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD]more[/TD]
[TD]more[/TD]
[TD]more[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[PilsburyKush]

If I'm understanding correctly, this is much more complicated than using VLOOKUP. Secondly, the way you want the data formatted is not impossible, but is not going to be any more efficient than copy and pasting. As for the lines of data you are requiring, an IFS statement would do the trick, implying you add the name of the workbook to every line of data as a reference point for your formula. Your IFS statement would require you to reference the name of the character and check to see if the "value to match" is negative. If this is correct than you could populate desired cells. But, Excel doesn't know how many negative numbers are in you data set therefore if you did a count on negative numbers in your data set and allotted the appropriate rows to each character you could fill the formula down through those cells.
I hope this makes sense and is of help!