OFFSET of result from vlookup

[danovkos]

Hi,
pls. can you help me? I try to figured out, how to return value from specific column in table with FORMULA. But,
I need to do something like use vlookup, and the result from vlookup will be starting position for offset. I dont know if am i clear.

e.g.
in column A are names, in B are IDs, C are Surname. I looking in B in IDs. I will found e.g. ID 5 in 5. row, and now i need to use offset and return Name from column A in row 5. In. other case i need to surname from column C in row 5. It have to be in the same row.

I try to googled, but was not succsessfull. I found something with index, but it didnt helps, then address...also ..
pls. any suggestions?

thx

[shrivallabha]

You found out the right function i.e. INDEX.

Suppose you were using VLOOKUP like this:
=VLOOKUP(F2,B2:B10,1,0)

Then INDEX shall be written as:
=INDEX(A2:A10,MATCH(F2,B2:B10,0))

[danovkos]

thx for try.
I am affraid, that my explaneition was bad.
Here is a sample.

i try to find formula, that will return this result.
In this table i will look base ID.
e.g. base ID 3.
It looks where ID 3 is, then it looks in columns H:J and choose max. of those 3 values (dates) and then, base if will result date1 or date2 or date3, return note1, note2 or note3.

e.g. id 3 - max of date is date3 3/10 so formula will return note 3 (because max of date was date3).

I know, maybe it is sci-fy ...
but thx for any suggestions...

[shrivallabha]

Assuming that your search ID is in cell A10, in any other empty cell write following formula:

=INDEX(E1:G7,MATCH(A10,A1:A7,0),MATCH(MAX(INDIRECT("H"&MATCH(A10,A1:A7,0)&" :J"&MATCH(A10,A1:A7,0))),INDIRECT("H"&MATCH(A10,A1:A7,0)&":J"&MATCH(A10,A1: A7,0)),0))

[danovkos]

thx a lot, i will try it


OK, it works in my dummy data, but when i try to use it in my wb, it return N/A
is it because i use data from other sheets?

this is my formula.
[VBA]=INDEX(zrkadlo!$CE$3:$CG$10000;MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0);M ATCH(MAX(INDIRECT("CK"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)&" :CM"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)));INDIRECT("CK"&MATCH(view! $B$1;zrkadlo!$CC$3:$CC$10000;0)&":CM"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$100 00;0));0))[/VBA]

what can be wrong?

[shrivallabha]

Try [untested]

=INDEX(zrkadlo!$CE$3:$CG$10000;MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0);M ATCH(MAX(INDIRECT("zrkadlo!CK"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)&" :CM"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)));INDIRECT("zrkadlo!CK"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)&":CM"&MATCH(view!$B$1;zrkadl o!$CC$3:$CC$10000;0));0))

Edit: Red marked part is added.

[danovkos]

I tried it, but still the same error. N/A.
But i am very thankful for your patience.

I try also fix it but, i am not successful. But i will try again.

Do you have any idea, where the problem can be ?

[VBA]
=INDEX(zrkadlo!$CE$3:$CG$10000;MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0);M ATCH(MAX(INDIRECT("zrkadlo!CK"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)&" :CM"&MATCH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)));INDIRECT("zrkadlo!CK"&MAT CH(view!$B$1;zrkadlo!$CC$3:$CC$10000;0)&":CM"&MATCH(view!$B$1;zrkadlo!$CC$3 :$CC$10000;0));0))
[/VBA]

[shrivallabha]

Try:

=INDEX(zrkadlo!$CE$1:$CG$10000,MATCH(view!$B$1,zrkadlo!$CC$1:$CC$10000,0),MATCH(MAX(INDIRECT("zrkadlo!CK"&MATCH(view!$B$1,zrkadlo!$CC$1:$CC$10000,0)&" :CM"&MATCH(view!$B$1,zrkadlo!$CC$1:$CC$10000,0))),INDIRECT("zrkadlo!CK"&MATCH(view!$B$1,zrkadlo!$CC$1:$CC$10000,0)&":CM"&MATCH(view!$B$1,zrkadlo!$CC$1:$CC$10000,0)),0))

If this works then probably MATCH was not setting correct references.

Otherwise, remove sensitive data and post your workbook for us to see.

[danovkos]

This formula also doesnt works. And i can not post my wb, because it is about 20 MB big. And a lot of buttons, macros, sensitive data and so .

I will try, till it will works...because i need. I hope i will figured out where the problem is.
thx