Vlookup a bit modified

[Spurious]

Hey,

I need my vlookup to lookup the value in one row, but return the column value one row below.

Is there a possibility?

Thanks

 

[baitmaster]

this would be possible using INDEX(MATCH( instead of VLOOKUP

Use MATCH to find the location of the value in column 1
Use Index to return that item from column 2
You can then add 1 to your match value to offset it

 

[Saagar]

Yes. This should get you started.

Use MATCH function to find the row number of the lookup value.
Use INDEX function (MATCH function + 1 will supply row number) to locate what you want.

Your function will look like this...
= INDEX(array,MATCH(.....)+1)

 

[Spurious]

Will try and report back! Thanks for the help.

 

[Spurious]

Ok, I got difficulties:

=VLOOKUP($A2;'GS Auswertung - 1'!$A$2:$AC$55;(COLUMN(C$1)+1);FALSE)

How do I translate that?

=MATCH($A2;'GS Auswertung - 1'!$A$2:$AC$55;FALSE)

This was my start, but it returns error. It is important to note that the columns is changing as well.

 

[baitmaster]

try =MATCH($A2;'GS Auswertung - 1'!$A$2:$A$55,0)

then =INDEX('GS Auswertung - 1'!$AC$2:$AC$55,MATCH($A2;'GS Auswertung - 1'!$A$2:$A$55,0))

Note that VLOOKUP uses range of multiple rows and columns

MATCH picks row x from the list in column A
INDEX returns the xth value from the list in column AC

 

[baitmaster]

Noting that you need to change columns too, INDEX can also work on a 2d array as well as a single column array (/list)

=INDEX(array,x,y) where you can use MATCH (or another approach) to return values for both parts of formula x and y, if needed

e.g. =INDEX(array, MATCH(...),MATCH(....))

 

[Spurious]

This works so far, only now I want it to return the value from the very column the formula is (starting B) + 1. I hope that makes sense.

Oh, I see you mention this. Basically there is a value in Row 1 that I want to find in the second sheet and then return the value from ("C" & column+1)

 

[baitmaster]

so you need the second dimension of the index related to the column number you are in?

try messing around with COLUMN(), and COLUMN()-COLUMN(A) etc - the second approach creates a relationship between the column the formula is in, and the column your lookup value is in

watch out for circular references though...

 

[baitmaster]

Looking at your Edit, you might also investigate INDIRECT