PDA

View Full Version : OFFSET of result from vlookup



danovkos
02-28-2013, 05:01 AM
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
02-28-2013, 06:53 AM
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
02-28-2013, 07:13 AM
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
02-28-2013, 08:37 AM
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
03-01-2013, 01:26 AM
thx a lot, i will try it
:hi:

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.
=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$10000;0));0))

what can be wrong? :(

shrivallabha
03-01-2013, 09:11 AM
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;zrkadlo!$CC$3:$CC$10000;0));0))
Edit: Red marked part is added.

danovkos
03-03-2013, 11:56 PM
I tried it, but still the same error. N/A. :dunno
But i am very thankful for your patience. :doh:

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 ?


=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;zrkadlo!$CC$3:$CC$10000;0));0))

shrivallabha
03-04-2013, 01:02 AM
Try:

=INDEX(zrkadlo!$CE$1:$CG$10000,MATCH(view!$B$1,zrkadlo!$CC$1:$CC$10000,0),M ATCH(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
03-04-2013, 08:22 AM
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