whats wrong with Vlookup 2

[Cpreston]

Hi

I am trying to do a vlookup and it looks perfect

=VLOOKUP(I2,A:G,10,TRUE) but I am getting #N/A

I have looked at a lot of examples and it seems ok. Attached is an example row, in the real sheet there are 100's of rows.

Thanks in advance

 

[combo]

VLOOKUP searches the first column id specified range and returns value in the crossection of found row and indicated column.
So, if you plan to find value in column C and return respective contents in J, then:
- searched range should be set to A:J,
- index of column to return should be 8.


combo

 

[Cpreston]

Hi Combo

The column I want to find the number is D

So I adjusted my VLOOKUP as this =VLOOKUP(I2,A:J,7,FALSE)

Still getting #N/A

I tried 6 and 8 but still the same

Thanks

 

[Andrzejek]

Cell H2 has [tt]=VLOOKUP(I2,D:J,7,TRUE)[/tt]
Is that it?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

VLOOKUP(look_for, In_Array, Return_Column, Match_Type)

Vlookup looks looks for look_for in the left-most column of In_Array.

Andy's solution should work.

Or, try Xlookup().

 

[Cpreston]

That is correct however I see what you are getting at I think so have moved the =VLOOKUP to K2 now so it is not part of the range but still get #N/A

Thanks

 

[Cpreston]

Sorry misunderstood things I think and Im getting more lost by the minute, I have the first row working

Attached is how my test sheet is looking and I added a row in to see if I could replicate the 2nd row which is working. But the 3rd row does not work. I need to apply this to a much bigger spreadsheet so trying to get the basic things working.

Thanks

 

[spamjim]

I'm not following either example file.

You might try sharing 3 rows that show sample source data and then enter example values in columns K, L for what you want to obtain from a vlookup.

If you have 11 rows of data, your vlookup in L2 might be "=VLOOKUP(H2,$D$2:$I$12,6,FALSE)"

...but even this is nonsense because we're not matching or looking up a value on H2.

 

[SkipVought]

It looks like you're looking up the Callee number and then returning the Start Date and Time.

Is that true?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

The column I want to find the number is D

So the search range should start in col. D.

I added a row in to see if I could replicate the 2nd row which is working. But the 3rd row does not work.

In my formula builder I have info, that the table should be sorted for finding the best match (last argument = TRUE). So, for your table, spamjim's formula with FALSE works.

combo

 

[Cpreston]

Ok

Attached is a spreadsheet with abit more data

I want in Column H the names from from column J where the column D match the ones in column I.

My spreadsheet could well be over 1000 lines per month and Column D would not be as uniformed as it is in my example but the number in D 2 could be in many different rows. Hope that makes sense and many thanks for the assistance

 

[SkipVought]

So I see that your 3 Callee Numbers each have 3 Callees in the table rows.

We usually do not put the data that you show in columns I & J contiguous to Table Data like you have in columns A:H

But I ask, what is it that you want to lookup for each of these Callee Numbers? There are 3 rows of data for each.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Cpreston]

There are over a thousand rows. This is just a few examples, I cannot supply the real data as it is phone numbers.

In Columns I and J this is a list of numbers and the relevant Names that exist in our phone system for them.
There are more names than this but again it is just an example of what we have.
I am trying to get the names in column H where D = I

Thanks

 

[Andrzejek]

Based on your Test2.xlsx, the formula in H2: [tt]=VLOOKUP(D2,I:J,2,FALSE)[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Got it,.

I separated your data to make it more understandable. You have a separate table of Callee Names.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Cpreston]

Oh god I am so stupid of course I had it the wrong way round. Got it now, one more question if you don't mind, how can I make the VLOOKUP populate the column where data exists

=VLOOKUP(D2,I:J,2,FALSE) is how I have it, I may have 1200 rows so could put in =VLOOKUP(D22000,I:J,2,FALSE but the amount or rows may change so I dont want a lot of blank rows just showing N/A if it is less, of course it may be more than 2000
Many Thanks for the replies

 

[SkipVought]

You have 2 separate bodies of data. One is the table from A:H and the other is what you had in I:J that I moved to J:K

Each formula in column H has ONE lookup value, not a range of lookup values in D. You are looking up in a much smaller table than you are reporting.

BTW, I regularly use INDEX & MATCH rather than VLOOKUP because in my world the lookup column is rarely in the "right" place.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

If you don't like to see #N/A:
[tt]=IFERROR(VLOOKUP(D2,I:J,2,FALSE),"")[/tt]

combo

 

[Cpreston]

Perfect, works well many thanks for all your replies very useful as always

 

[Andrzejek]

=VLOOKUP(D2,I:J,2,FALSE) is how I have it, I may have 1200 rows so could put in =VLOOKUP(D[highlight #FCE94F]2[/highlight][highlight #FCE94F]2000[/highlight],I:J,2,FALSE

If you do that, your formula will not work, because now you would ask: "I have these 2000 cells with some values, give me 1 outcome of my formula in cell H5 (for example)".

What your formula =VLOOKUP([highlight #8AE234]D2[/highlight],[highlight #FCE94F]I:J[/highlight],2,FALSE) in H2 is doing: take a value from cell [highlight #8AE234]D2[/highlight], look at first column in the range of columns [highlight #FCE94F]I:J[/highlight], and if you find my (D2) value in (first) column I, give me the corresponding value from 2nd column (J).
Copy/paste the formula down the column H and you have your solution.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson