Excel - Vlookup?? Offset?? HELP!!! 1

[ladyck3]

Hey you gurus.... I'm in need of some assistance, I'll tell ya what we need for an end result, please help to determine how to get this to work... Vlookup is NOT cutting it.

Below is the data, A4 thru E12

AA 107.423 2.611 550,168.50 45
AA 109.916 2.889 1,128,053.38 79
AA 108.287 2.014 551,503.38 44
AA 115.285 2.049 1,173,335.38 72
AA 106.551 1.933 1,084,842.63 79
AA 102.553 1.015 828,548.06 52
AA 102.046 1.284 619,979.13 72
AA 108.36 2.175 331,605.88 67
AA 110.978 2.636 284,034.81 24

Say this is SHEET1.

On SHEET 2, in A1 and down I would like to return the
values in Column E,

45
79
44
72 etc

This formula does not work, it returns 45 in all cells, if I change it to TRUE, it returns 24 in all cells.

=VLOOKUP($A$1,Sheet1!A:E,5,FALSE)

Please Help.
And Thanks!

Laurie

LadyCK3

 

[bluedragon2]

What value are you using to lookup in the array and where is the value on the sheet that you are using as a reference to look up?



Blue

If I wasn't Blue, I would just be a Dragon...

 

[ladyck3]

Basically, Column A has many different abbreviations, for this particular formula, if A4:A12 is AA, then return the value in Column E, which is the 45, 79, etc

There are multiple pages in the workbook, AA refers to the data on the particular page in this workbook that I'm working on... the other abbreviations in Column A on INDEX would pertain to other worksheets.

I know this is kinda cryptic, isn't it, but its the best info I've been given.

So if on .. lets call the worksheet I'm working on TREES

In A1 on TREES, I want it to show the number on the INDEX tab in Column E that corresponds with each instance of AA in Column A...

From the table above, you will see that as a result, on TREES, A1 would = 45
A2 = 79
A3 = 44
A4 = 72 and so on....

So on TREES I simply want JUST the numbers from Column E which are related to the AA's in Column A on the INDEX sheet.

Hope this helps explain it...
Thanks Blue

Laurie


LadyCK3

 

[ladyck3]

Let me rephrase this just a sec...

ON TREES... in A1, we would type an abbreviation (any from Column A in the INDEX) and the results of that Abbreviation's data in Column E on INDEX would then appear in A3 thru A(whatever)

So we're grabbing the data from INDEX on one abbreviation and showing the resulting data from E on the INDEX page.

Yeah I think this might clear it up to coffee instead of mud, right?

Laurie


LadyCK3

 

[bluedragon2]

OK,I see what you are wanting, the VLOOKUP isn't going to work like you want it as you have found out. By putting FALSE, it will go to the first match and give you the value no matter how many times you run against the array.(ie will retrun 45 every time). By putting TRUE, it will result with the last instance everytime you run it (ie the 24). So doing this without a macro, we will have to adjust the VLookup for each run. But doing that, will result in a list with empty rows between data that doesn't match. You could do the same thing with an IF statement. Doing this will give you a list of the data you want, but you will have to sort it to get rid of the blank spaces. I will look a little deeper to see if I can come up with something else.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Try this:

=VLOOKUP($A$1,Sheet1!A:E,5,FALSE)

Instead of A:E, put A1:$E$(whatever)

put that in row one then copy down.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Wait, That won't work either, because you will duplicate data.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[ladyck3]

Well Blue... that's the formula we were using and the result you see ... there in lies the dilema

So I'm not sure how to orchestrate this... and I bet it will take scripting ...which I would rather have a formula than scripting... as per usual...

However, any input on this would be great because as a matter of fact, this will help me on another project I've been working on and put aside because we could not resolve this

Thanks for keepin' on keepin' on, Blue!

Laurie


LadyCK3

 

[bluedragon2]

I am going to have to tackle this one on Monday...

I am a little hungover from partying to much last night and the rows and columns are making me dizzy

Have a nice weekend...



Blue

If I wasn't Blue, I would just be a Dragon...

 

[ladyck3]

LOL... bad boy!

hehe
Well any help would be appreciated....

Where's that SKIP fella and that Ken Wright fella... this would, I would think, be something they'd LOVE to chew on!!

Laurie


LadyCK3

 

[Zathras]

Take a look at thread68-693835 for a version of my [blue]VLOOKUPNEXT[/color] function. If I understand you correctly, that should provide what you want.

 

[ladyck3]

Oh cool... gonna check it out, thanks Z... appreciate it!!!

Vlookupnext who woulda thunk it? hehe

Appreciate this!

Will get back to you..
Laurie


LadyCK3

 

[KenWright]

Not a problem if you can handle a helper column in there somewhere on that sheet, though you can hide it, or make the font white or whatever to disguise it.

With your data on sheet1 in A4:E1000

Assume helper column is on sheet1 in Col G. In cell G4 put the following formula and then copy down to G1000:-

=IF(A4=Sheet2!$A$1,COUNTIF($A$4:A4,A4),"&quot

Now on Sheet2 in cell A1 put your AA

Now on sheet2, select A4:A1000, or A4:Axx where xx is the max number of entries you could have for any of your codes.

With the range selected, paste the following into the formula bar:-

=IF(ISERROR(INDEX(Sheet1!$E$4:$E$1000,MATCH(SMALL(Sheet1!$G$4:$G$1000,ROW(INDIRECT("1:1000&quot)),Sheet1!$G$4:$G$30,0))),"",INDEX(Sheet1!$E$4:$E$1000,MATCH(SMALL(Sheet1!$G$4:$G$1000,ROW(INDIRECT("1:1000&quot)),Sheet1!$G$4:$G$1000,0)))

and array enter it using CTRL+SHIFT+ENTER

Now you should see every match.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[ladyck3]

Ken, I tried this and it works for the first instance..

Meaning I enter the iserror formula on the second sheet and it products the 45 (first answer) but when I copy the formula down that row, all answers are 45, it does not pick up the second, third, fourth, etc...

It should appear as:

45
79
44
72
79
52
72
67
24

Basically, the process you gave me has the same results as the fomula that Blue gave me, which is what we were working with in the first place.

There are multiple rows in Column A which have AA in them, and each has a different value in E, I need it to just put those different values, as show above, on the second sheet, JUST the data in E that relates to each occurance of AA in Column A.

I tried this once before for a different scenario... I downloaded a list of file extensions, and wanted to make a small lookup tool, so if you enter, for instance, "DOC" it would tell you that it was an extension for a WORD file... but DOC pertains to others as well so there is more than one reason for DOC to exist.. I wanted it to return all reasons... could never get that to work either... this is the same concept.

The data I have is confidential, I wish that I could send it to someone but I can't.... Please ask whatever questions you wish in order to get this on straightened out... I really DO need help with this.... You know that, sorry to try and impress the fact that I'm desperate..on you

Thanks in advance everyone...
Laurie


LadyCK3

 

[KenWright]

You are entering it incorrectly. You are entering the formula into a single cell and then copying that cell and pasting it down. This means that you didn't read my note carefully now did you

My note said:-
>> With the range selected, paste the following into the formula bar:-

You actually need to select the whole range, as far as you think you need to go, and then with that range selected, paste the formula into the formula bar and array enter it using CTRL+SHIFT+ENTER. This will put the formula across that range and you won't need to do any more copying or pasting.

Regards
Ken.............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[ladyck3]

Duly scolded ... it works and I THANK YOU... I had to modify it a bit for the real data but it works swimmingly and I really really appreciate it!!!

::shaking finger at self:: READ INSTRUCTIONS LAURIE... and for pete's sake REMEMBER when you go to execute them.

hehe

Thanks Ken... already gave you a star, guess I nuked the browser window before actually submitting my thanks yesterday so I thought I best do that today!!!

Laurie


LadyCK3

 

[KenWright]

Regards
Ken.............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------