Compare a cell against a column and grab date from another column help?

[whispers]

I have read and tried about match() and vlookup()..
but none have seems to work..o at least correctly.. (close though)

this is the line/formula that works for me the best so far;

=IF(ISNA(VLOOKUP(A2,K:K,20,FALSE)),T2,"NO")

which I 'think' is working.. except that the T2 part is hardcoded.. and increments each cell I applied this formula too.. it needs to be the T* (* = dynamic, and be the same row as the match was found in 'k'.. if that makes sense?)

overview of what Im trying to do incase ther is a better way r it helps describe my goal.

I have a column "A".

with say 200 cells in it..

column b is where I am putting this formula in (b2 currently)

column c, d, e, are blank.. (going to be populated with data eventually after I get this formula to work)

for each CELL/VALUE in column A

I want to compare/search/match/vlookup (whatever works best) against ALL the values in column "K" (which might be 2-3000+ rows long)

If a match -is- found in column "K" matching the value in A2.....I want to grab the values from various other columns.. (H, J, T, S..etc)

and populate the cell that has the formula in it...

then of course I want to do this for A3, A4, A5..etc.. (I would just drag the formula down the column)

how can I get T2 to be DYNAMIC and not just increase to T3, T4, T5..etc.. but always be T + (whatever rows the matching cell in K was found at)
?


thanks!

[TMS]

=IF(ISNA(VLOOKUP(A2,K:K,20,FALSE)),T2,"NO")


No, that cannot work. The second parameter is the range where the data resides and the third is the column to be returned. But the range is only one column wide (column K) so you can't return the 20th column. The search is made against the first column in the range.

Regards, TMS

[Jarko28]

it sounds like vlookup should work if you want to look for K and get the corresponding value of column T from the matched row:

=IFERROR(VLOOKUP(A2,$K:$T,10,FALSE),"NO")

If you need to look at a value in column K but return a value from columns that are to the left of column K, you will need to use a combination of the index and match functions. If you post a sample with sensitive data removed and show desired output, it would be easier to determine what you need.

[whispers]

thanks Jarko28-

I'll have to make up some data.. as the real spreadsheet is at work..

but I want to:

look/take value in: A2

check/compare that value in A2, against the whole COLUMN of 'K'...

'IF' a match is found (in column K)..... then return/display the value(s) in columns (S, T, X, Y, Z...etc) in the same 'row' the match in column "K" was found

(ie: A2 = Doe, John.... and a match is found in cell K1228 = Doe, John.. I need to take/return the values in other columns L1228, N1288, S1228, T1228)
(ie: A3 = Doe, Jane.... and a match is found in cell K66 = Doe, Jane.. I need to take/return the values in other columns L66, N66, S66, T66)
(ie: A3 = Bob, Billy.... and a match is found in cell K567 = Bob, Billy.. I need to take/return the values in other columns L567, N567, S567, T567)

etc..etc

all the way down the line for as many cells there is in column A..


hope this helps until I can try and make up some data or post the real deal..


so to re-iterate the adive..and your formula:

=IFERROR(VLOOKUP(A2,$K:$T,10,FALSE),"NO")


the $k is the column Im check the value in A2 against...yes?
$T is the value I return 'if' a Match (A2) is found in column K......yes?
what is the '10' for? (guess Im not understanding how to properly use that parameter)

if ther is not match (FALSE).. the display 'NO'.....correct?



thanks!

[whispers]

ok.. well I re-created a quick example..

tried this, and doesnt work:

=IF(VLOOKUP(A2,$K:$T,10,FALSE),T2,"NO")

displays a #VALUE error in the cell..

her is a quick, dummy mock up of the data..

formula_demo.xls

again I wanna take the value in column A (cell by cell).. for this example we'll say A2 (which has text of Doe, John)

I wanna take A2..and search all of column K to see if there is a match,.. if I match is found.. I need ot knwo what ROW.. so I can grab/display the data in SAME ROW>. but in columns after the "K" (match) column..


thanks!

[Jarko28]

that's not the formula i provided...you went back to your formula and it won't work because you are trying to pass an if argument without giving it a condition...see attached.

[whispers]

hi.. in the example I uploaded.. that was at my last point of trying different formulas..

I did try yours..didnt work.

I looket your example.. and that formula is different?

what does this part do?

=_xlfn.


and also why if I removed form the second line (A3) but not in A2).. do they BOTH display #NAME then?

hm.. actually when I dont even do ANYTHING to the cell but check on the formula you used.. and then hit enter (making no changes).. it gives the #NAME error?

(on this home computer.. its an OLD version of excel..is that the problem perhaps?)

thanks..


*I can try from a diff computer upstairs with excel 2007 on it I think..

**also how can I grab more than the "T" column? can I grab multiple rows? from this example..lets say L, P, S, T columns? all same 'matching' row as whatever row in found in 'k'..?

[Jarko28]

the iferror function doesn't exist prior before Excel 2007...your profile says you use 2007...see that attached to see if it works on the older version...

[whispers]

I just happen to be on a machine at home that has an old copy of office on it..


I'll try on newer version (2007) as well as give this older (legacy) version a try..


any thoughts on how I can grab multiple columns when a match is found? instead of just "T"?..

but (using this spreadsheet as example) columns L, P, S, T?

thanks

[Jarko28]

Look at the last sheet I attached, it should work on your older version of excel...

[whispers]

FYI.. this formula for the older/pre-2007 versions of excel works great!!

=IF(ISNA(VLOOKUP(A4,$K:$T,10,FALSE))=TRUE,"NO",VLOOKUP(A4,$K:$T,10,FALSE))

re: multiple column values-
ahh.. I see, DUH!.. just make another column and update the formula! (and I was stuck on trying to add/concatenate those cells/values into 1!)

I understand how the range and the 'index' value work now too!


** will this also work when I try to use in excel 2007 at work tomorrow? (backwards compatible?)


thanks!

[Jarko28]

great, glad it worked out

[acerrhod]

you may try this:
in Column B
=INDEX($T:$T,MATCH($A2,$K:$K,0),1)

in Column C
=INDEX($L:$L,MATCH($A2,$K:$K,0),1)