Is there an easier way? LOOKUP

[afarris89]

Hello!
I am using a lookup function to extract a few pieces of data from a spreadsheet i receive monthly. Currently, to use my function, I first have to define my arrays before I can sort into an ascending order. All in all, this requires about 200 arrays to be defined to use a rather simple lookup function: =LOOKUP("Logged-in",andrea1,andreaT1) Andrea1 being the whole data set for Andrea and AndreaT1 being one column of data in Andrea's data set. Am I using the wrong function entirely? Am I missing a key concept of the lookup function? Any input is appreciated.
Thanks!

 

[Jonmo1]

What are the actual ranges referenced by the 2 named ranges andrea1 and andreaT1 ?
Both need to be single column ranges
andrea1 being the column to find "Logged-in"
andreaT1 being the colunt to return a value from based on the row where Logged-in was found.

 

[afarris89]

What are the actual ranges referenced by the 2 named ranges andrea1 and andreaT1 ?
Both need to be single column ranges
andrea1 being the column to find "Logged-in"
andreaT1 being the colunt to return a value from based on the row where Logged-in was found.

I guess that I have been doing this incorrectly. I have been using a3:g23 for Andrea1 (roughly, the number of cells changes each month so a range would not work) and D3:d23 for AndreaT1. The thing is, this works just fine for me, my issue is the amount of arrays that I have to define. I have to make those two definitions for each person and for each day on my list. By the end of it, its 200 arrays that have to be defined. Is there something more efficient that i can be doing to achieve the same result?

 

[Jonmo1]

ok, that acutally works, but after a fasion of Excel making adjustments, guessing at what it thinks you meant.
It's really only looking for the match in A3:A23 (the left most column of A3:G23), it won't find it if it's in say C15.

Realistically, the 'correct' way to write it would be
=LOOKUP(value,A3:A23,D3:D23)
or even
=LOOKUP(value,A3:D23)


But the way you're doing it does work, after Excel assumes you meant to reference only the leftmost column of the array.
And as long as you understand that, and you're not expecting it to find the match in a column other than A, then...
If it ain't broke, don't fix it.

 

[afarris89]

ok, that acutally works, but after a fasion of Excel making adjustments, guessing at what it thinks you meant.
It's really only looking for the match in A3:A23 (the left most column of A3:G23), it won't find it if it's in say C15.

Realistically, the 'correct' way to write it would be
=LOOKUP(value,A3:A23,D3:D23)
or even
=LOOKUP(value,A3:D23)


But the way you're doing it does work, after Excel assumes you meant to reference only the leftmost column of the array.
And as long as you understand that, and you're not expecting it to find the match in a column other than A, then...
If it ain't broke, don't fix it.

I just tried doing this by defining single column arrays but this continues to give me an #N/A error message. Ive tried to sort in a number of ways but nothing is producing a result by doing this. Is this something i would have to use cell ranges for?
Thanks for all your input!

 

[Jonmo1]

I didn't know you were actually getting #N/A errors. I only got the impression that you were asking if this was the best way..

Anyway, with Lookup, it assumes the data is sorted in Ascending order by the leftmost column.
If it's reurning #N/A, that means the lookup value is SMALLER than the Lowest value in the range.
Or that lookup value is a different data type than what is in the lookup range, i.e. Text Vs Number.
Use Isnumber(A1) to test if a value is a number or text

 

[afarris89]

Oh that only started happening when I tried the correct way you just taught me. ha! my way works just fine, its just very time consuming.

 

[Jonmo1]

Maybe I'm misunderstanding what you're actually asking..

What do you mean

I have to make those two definitions for each person and for each day on my list

Can you give examples for at least 2 users ?
Defined names and the actual ranges they refer to, and working formulas.