Need cell after the vlookup up results 2

[FireGeek21]

How do I find the data in a cell after the vlookup results? For example, my vlookup produces the value in C100, however it is truely the value in C101 that I need. How do I get to C101?

THANKS!!!!


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[kjv1611]

I'd guess you need to look at the Row value for VLOOKUP given address, and then increment that, then add back in the column and the newly calculated row value.

Not sure right away whether this is allowable with VLOOKUP, but I'd imagine, it'd be something like:

=COLUMN(VLOOKUP(...)+(ROW(VLOOKUP(...)+1)

I don't know if that sort of context would work, but the general idea should if you can find the correct context to use.

 

[Gruuuu]

Your Vlookup formula looks something like this, I assume
=VLOOKUP("value",Range,#,true/false)
the # is the number of columns across from your "value"
increase that number by 1.

If there is not an actual number in there, you may insert a +1 into whatever the reference is
For example

=VLOOKUP("blah",A:H,[red]$A$1[/red],false)
=VLOOKUP("blah",A:H,[red]$A$1+1[/red],false)

 

[Gavona]

Use Index & Match. Match will return the row number,(so you can add 1 to get the next row). Have a look at the examples here:

http://www.contextures.com/xlfunctions03.html

Gavin

 

[SkipVought]

I would DEFINETELY endorse INDEX & MATCH. I actually use INDEX & MATCH almost exclusively, rather than VLOOKUP, since 1) I ALWAYS use named ranges and 2) it matters not, what order the fields are in.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FireGeek21]

Thank you everyone for the replies! I do have an update... solution & problem found!!!

Here was my ultimate solution

=INDEX(AllData!D, MATCH(A2,AllData!A:A,0)+1)

All along I was trying to use INDEX & MATCH but it wasn't working - thus the post here. After several hours of plunking away, probably time I should have been taking care of the data manually, I found the source of the problem.

The data export I was working with had the id number (lookup value in this case A2) in the detail rows as a text type and the id number in the subtotal row as a number type. MATCH was finding the subtotal row and the whole formula was then looking to the next row giving me the next id number's information when I wanted the first instance of the number (not subtotal) and then the second row. Changing the data type to number didn't want to fly either! I had to change the whole column to a text type and then it worked!

The other problem was handling serial dates in the INDEX array. The dates appeared to be normal dates - 09/01/2008 but were actually stored as serial dates (found when copying and pasting special -> values). I had no idea what serial 40047 was so I was also trying various things to avoid the serial number being returned. ARUGH!

Anyway, thank you for the help! Hope my experience with data types helps someone else down the line! Here is the formula that ended up working for me...

=INDEX(AllData!D, MATCH(A2,AllData!A:A,0)+1)

RSGeek



RSGeek
(currently using Crystal Reports XI with Lawson 8.03)