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:

Text	text	text	value to match
foo	foo	foo	-1
bar	bar	bar	0
foobar1	foobar1	foobar1	-2
helloworld	helloworld	helloworld	4

<tbody>
</tbody>

John Snow <--worksheet name

text	text	text	value to match
foobar1	foobar1	foobar1	3
anothervalue	anothervalue	anothervalue	-4
something	something	something	2
more	more	more	-2

<tbody>
</tbody>

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:

John Snow
foo	foo	foo	-1
foobar1	foobar1	foobar1	-2
Elon Musk
anothervalue	anothervalue	anothervalue	-4
more	more	more	-2

<tbody>
</tbody>

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!