VLOOKUP Retuning same value 1

[kcoleman26]

Excel 2010

I am creating a tab on a spreadsheet that you can type in a Report # in one cell and get all of the information listed to display on that tab. I know filtering could do the same thing but I am also bringing over other information in which I am calculating on. The end result should be a printable document with everything listed and caculated for a final report.

Log Tab
Report# Category
001 Bug Fix
002 Bug Fix
001 Formatting

Report Tab - Type 001 into one cell say A1 and then the report will display all results

Bug Fix
Formatting

What my report is showing is
Bug Fix
Bug Fix

Please forgive me on the formula as I know you can't see the cells.
=VLOOKUP($A$1,A2:$B$6,2,FALSE) so when I copy the formula from B6 to B7 I get the same result

 

[SkipVought]

Hi,

Lookup functions alone won't do what you want.

I'd recommend using AutoFilter and appending the additional info to the right. Since you're just printing a report, there is no need for individual sheets as you propose, IMNSHO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

If you don't want to use Filters, making a simple macro might be the easiest way to extract the information you want.

 

[kcoleman26]

Might have to go with a Macro. Guess I gotta dust off those skills sometime it has been a while. Thanks

 

[byundt]

You could return the desired information using an array-entered formula like:
=IF(COUNTIF(Sheet1!$A$1:$A$1000,$A$1)<ROWS(F$1:F1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A$1,ROW(Sheet1!$A$1:$A$1000)-ROW(Sheet1!$A$1)+1,""),ROWS(F$1:F1))))

I put the formula in cell F1 and copied it down. The formula assumes that the report number is listed in cell A1 on that same worksheet, and that the lookup table is in Sheet1 columns A and B. The formula returns an empty string (looks like a blank) after all the data has been returned.

If you copy the formula across, it will return data from Sheet1 columns C, D, etc. while still looking for a match in column A.

To array-enter a formula:
1. Paste the formula in the formula bar (or click in formula bar if formula is already there)
2. Hold the Control and Shift keys down
3. Hit Enter key, then release all three keys
Excel should respond by adding curly braces { } surrounding your formula. If not (or if you see #VALUE! error), then repeat steps 1 to 3.

Note: although you could "simplify" the formula by letting it refer to full columns, you will notice very sluggish response as Excel performs the calculation on a million rows. I limited it to the first 1000 rows on the worksheet for a snappy response; the 1000 is arbitrary, but should extend beyond any expected amount of data.