Vlookup return value from a different row

[yrndtn]

Hi,

I'm trying to use the VLOOKUP function to return value from a row below.
I have a large list of numbers (sheet Data) for the entire year with 8 different items per week, and I need to create a list for each week with group of 8 unique items as shown on "Week 21" sheet (I did it manually by copy and paste, highlights are just for the example, don't need to be at the original file). After the first 8th rows, it should start over until it gets to the last value on sheet "Week 21".
Basically, Column B on sheet "Week 21" should be statics, and once I change value on column A it should return values as in the example automatically.

Thanks

[zbor]

Look here... Extend as you need:

[yrndtn]

Great, Thanks.
How do i mark it as "SOLVED"?

[zbor]

Go to first post -> Edit -> Go advanced -> chooose [solved] from drop down menu

[crazyaboutgus]

zbor,

Have you ever seen something like this? It takes the next column up. It's driving me mad.

[zbor]

It takes the next column up.

That's because your input and vlookup array doesn't match:

In A column you have it0001
In G column you have it0001 with few spaces at the end.

When you using VLOOKUP function make sure what result do you expect:

Do you need EXACT match or APROXIMATE match.

If you need exact match you need to put FALSE at the end of formula:

=VLOOKUP($A2;G1:H8806;2;FALSE) (wich will give you error in this case because your A2 and G column doesn't match.

If you need aproximate match

=VLOOKUP($A2;G1:H8806;2;TRUE) (or if there's no TRUE formula assume it's TRUE)

1) your data must be sorted in ascending order
2) if there is no match it will return first lowest value (in this case H1)

Like...
if you look for letter C in ABD it would return B...


Also I assume you need to have locked range:

=VLOOKUP($A2;$G$1:$H$8806;2;FALSE)

because your formula will return error if it0001 is for example in row 20 (and everything else works)...

For this particular case try:

=VLOOKUP($A2,SUBSTITUTE($G$1:$H$8806," ",""),2,FALSE)

comfirmed with ctrl+shift+enter

However, this is not very efficiant solution so best way would be to select G column -> ctrl+H -> (Find what) put space in -> Replace with (Leave blank) -> Replace All (In other words replace all spaces in column G with nothing)

This is how your original formula will work only adjust it:

=VLOOKUP($A2,$G$1:$H$8806,2,FALSE)

This will work for exact match. If there is no exact match you need to decide what to do:

1) to have error
2) to return first lower value (see above)

[opsman]

I've been looking at this too and it's been driving me a little scatty whilst waiting for help on another issue.... however (and I may be wrong) I seem to remember my ex boss telling me something about the first line of lookups and beware with them, so whilst I can't be of much help I think it may be an "excel" thing. I think that's why he always left the first line of his spreadsheets empty....

I have tried playing around with your attachment, but I couldn;t get the thing to work yet.

[Hollinshead]

hi there, i wounder if you could help me with what you may seem is a simple formula. I am trying to create a formula for a cell that caluclates the total amounts in a certain columns that are named a certain value in another column. I know that may not be that clear so heres an example.

Column 1 (Business Area)
Risk & Resilience
Performance Consulting
Performance Consulting
Performance Consulting
Systems solutions

Column 2 (Gross Profit)
45,000
68,500
100,000
500,000
10,000,000

So the total amount for Risk & Resilience is 45,000 and the total for performance consulting is 668,500.

I just need a formulas for a cell to automatically calculate this. Any ideas?

Thanks for your time and efforts in advance.

[zbor]

Hi Hollinshead,

please check forum rules. Your post don't match rule number 2:

2. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.