need help with a simple answer (1 Viewer)

[mattg]

hello people

i am using the vlookup function to return a value from my database

its great because it does exactly what i want

but if i wanted a function to skip the first searched criteria and go to the next one what do i need to do

and again to miss the first 2 and search the third

i have multiple records for the same name

john 234
john 345
john 224
dave 112
dace 113
dave 115

 

[NBVC]

A couple of weeks old... but.. just in case still needed...

If you have the table in say A2:B7

then if you have the input name in C2, try:

=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=C2,ROW($A$2:$A$7)-ROW($A$2)+1),2))

you need to then confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around it.

change the last 2 to a 3 for third, etc...

If you want to create a formula and copy down to extract 1st, 2nd, 3rd, etc...

=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=C2,ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($A$1:$A1)))

confirmed with CTRL+SHIFT+ENTER.

 

[mattg]

wow. i supose it helps if you actualy know what you are doing, if i had to name my experience it would be novice begginner

my data table is in (data) a2-aa65000

my next sheet is called figure

in the figure sheet i paste the name into a2

i use the vlookup to look up the name in figure sheet a2 then go to data sheet were the names are all rowed in a2-a65000

once its found the name it returns the figure frim cell 28 and returns a false value

so would it be ]

1ST NAME
=INDEX($A$2:$A$65000,SMALL(IF($A$2:$A$65000=A2,ROW($A$2:$A $65000)-ROW($A$2)+1),ROWS($A$1:$A1)))

2ND NAME WHICH IS THE SAME

=INDEX($A$2:$A$65000,SMALL(IF($A$2:$A$65000=A2,ROW($A$2:$A $65000)-ROW($A$2)+2),ROWS($A$1:$A1)))

3RD NAME WHICH IS THE SAME

=INDEX($A$2:$A$65000,SMALL(IF($A$2:$A$65000=A2,ROW($A$2:$A $65000)-ROW($A$2)+3),ROWS($A$1:$A1)))

THEN CTRL SHIFT ENTER

SORRY TO BE A PAIN AND THANKYOU FOR TAKING SOME OF YOUR TIME TO ANSWER THIS, IT IS AN OLD POST BUT YOU HAVE BEEN THE ONLY ONE WHO HAS ANSWERED TO WHICH I AM TRUELY GRATEFULL

 

[Brianwarnock]

mattg

I did not understand this

once its found the name it returns the figure frim cell 28 and returns a false value

Now it looks like your cells contain a name and a number eg John 234 and you want to search just on the name.

Let me first suggest that you read Excel help on all of the functions that appear in the formulae Rows($A$1:$A1) for example is indicating which occurence of the name to return.

Now I would name your data array, say array1 then I would not use Rows as NBVC did or fiddle with the starting postion, so assuming that you want the data returned in sheet figues!c1 type in there for the second occurrence of the name in figures a2

=INDEX(array1,SMALL(IF(LEFT(array1,FIND(" ",array1)-1)=A2,ROW(array1)-1),2))

I would type in the kth, as help descibes it, value 2 or 3 etc

Brian

 

[mattg]

I did not understand this

Quote:
once its found the name it returns the figure frim cell 28 and returns a false value

once its found the name it returns the figure frim cell 28 and returns a false valuthanks for all your help, i dont think im explaining it right so here we go

i have a spreadsheet excel..i have two sheets ,figures and data

on the figures page in column a i paste the name i want refrencing. in column b i have a vlookup code which is vlookup;a1;a1:ak65000;10;0

so basicly the vlookup searches the name i paste in cell a1 in figures then seraches the names on sheet data and returns the value from cell 10 using a false value (0)

on my data sheet has 10 columns long and 65000 rows down

example
john 18
john 12
john 11
john 10
dave 10
bill 9

so now i would have the name john in cell a1 in the figures sheet and in column b cell 1 the figure from the data sheet is now displayed (the first name it finds using vlookup would be john 18)

now what i would like to do is have the code put into cell c1 so it searches for the name in cell a1 figures but misses the first name it finds and returns the same value from the next name in line which would be john 12

so now my sheet would read

a1=john b1=18 c1=12

then repeat the code again in column d cell 1 but find the third name it finds

so now my figures sheet would read

a1=john b1=18 c1=12 d1=11

the idea is i have to check the last three numbers from certian names on a daily basis,its around 100

so all the codes will stay in column b,c and d and saved ,and all i have to do is paste the names into the figures sheet on a daily basis and get the last three transactions from every name i paste in ....i can then export the info out and use it in different spread sheets for different things .

this is not something i am greatly clued up on so if it takes alot of work then obviously i can pay for this privilage

thank you again for being patient

 

[Brianwarnock]

vlookup;a1;a1:ak65000;10;0

so the three values can appear anywhere in that range?

That's a whole different problem as i'm not sure the trick with Small can be adapted for that, at the moment we are returning a row to the index but now we need Row and column.

Brian

 

[Brianwarnock]

reading the thread again I found this

i use the vlookup to look up the name in figure sheet a2 then go to data sheet were the names are all rowed in a2-a65000

so assuming that we are only searching in 1 column see the attached.
You will need to change the Array1 definition to cover all entered rows.

I think that you will need to understand a number of functions to follow this "simple" (from post 1) solution.

Brian

 

[mattg]

you are spot on brian apart from the names are in coloumn a and the figures are in the same row but in colmun 25


see my database and it will make sence as you are basicly there

again many thanks

 

[Brianwarnock]

Bloody Hell I've wasted my time, did you look at what i was doing all that busineess with left Right etc because i thought, and you did nothing to put me right, that col A contained a name and number

From my first post

Now it looks like your cells contain a name and a number eg John 234 and you want to search just on the name.

all you needed to do was add the column index 25 to the INDEX function some way back.

I'm too frustrated to do anything at the moment.

Brian

 

[mattg]

you are spot on brian apart from the names are in coloumn a and the figures are in the same row but in colmun 25


see my database and it will make sence as you are basicly there

again many thanks

ps =all the names are in alphabetical order, so all the johns will be together

sorry brian but you are talking to me like i know what im doing. if i did i wouldnt be here. my knowledge stretches as far as your arm

now you know why my i.t bloke gave up....lol

 

[Brianwarnock]

ps =all the names are in alphabetical order, so all the johns will be together

sorry brian but you are talking to me like i know what im doing. if i did i wouldnt be here. my knowledge stretches as far as your arm

now you know why my i.t bloke gave up....lol

None of us know everything but we come here to learn and get help in that process, that requires us to read carefully what is written.

I have calmed down sufficiently to put the formulae into your spreadsheet, after you have studied it feel free to ask questions.

Brian

 

[mattg]

brian your the man, thankyou for the following

a=being patient
b=doing my coding
c=not giving up on me

i will be able to save over 30minutes a day now ..

again i cant thank you enought

top bloke..

 

[mattg]

hello again , just as you thougth your headache had gone.

=INDEX(ArrayAll;SMALL(IF(arraya=​

​

​

​

$A2;ROW(arraya)-2);1);25)

this works great in the datasheet you sent me,but when i copy it into my database it finds the first name but

allowance 1 returns the figure two rows up from the first name

allowance 2 returns the figure two rows up

allowance 3 returns the figure which should be allowance 1

ive tried copying my database into the datasheet and it does the same.

i have set all the arraya and the array all on the datasheet as advised and manualy entered the code using the close thing after ctrl,shift,enter

​

 

[Brianwarnock]

What happened to the font size. I've only got central vision in 1 eye and that is not too good so no small fonts please.

If I got time today I had intended to write an explanation of the formulae but for now

The reference point of the INDEX and the Small are different
The Index rownum is within the array, the Small is within the worksheet the number in red is to adjust for this ie the Small would return 5 for the first row but the array actually starts in 3 , this is in the sheet I worked on, I assume that yours is different?

=INDEX(ArrayAll;SMALL(IF(arraya=$A2;ROW(arraya)-2);1);25)

Brian

 

[mattg]

hello again, sorry for the small text

its working fine in the sheet you done me, i pasted in all 30,000 entrys into the data sheet which stretches from a2:ab30,000

then in the figures sheet i copied the index values from b2:d200

now if i copy the horses from the data sheet they all come back with the right info.. so it is all working properly

the problem starts when i try pasting in the names i need on a daily basis which come from a different spreadsheet with exactly the same font.THESE ONCE COMPLETE GET PUT INTO THE DATABASE AS NEW ROWS OF INFO

i have tried entering the index value into my database but i have that problem which returns the value two rows up, so i will fiddle with that so all the array is set properly as suggested.

i have a great book called excel as my database and i am trying to understand it all, im ok with lookups ect but this array things a different ball game .

=INDEX(ArrayAll;SMALL(IF(arraya=$A2;ROW(arraya)-2);1);25)

 

[Brianwarnock]

Without seeing the spreadsheets I cannot say what is going on.

Does this help

=INDEX(ArrayAll;SMALL(IF(arraya=$A2;ROW(arraya)-2);1);25)

ok
INDEX(array,rownum,colnum)
Small(array,k) where k is the kth smallest , this is really designed for numerical arrays, NBVC cleverly used it to find the smallest row number which ROW is giving us.

If(arraya=$A2,ROW(arraya)) is saying for each match in the array return the rownumber, now this is the Excel worksheet row number so as I said earlier we need to adjust for the INDEX rownumber which is relative to the start of the array ArrayAll so it becomes in the instance I looked at, If(arraya=$A2,ROW(arraya)-2) as the arrayall starts in Row 3. We then Select the kth smallest, thus
Small(If(arraya=$A2,ROW(arraya)-2,1)) is the Rownumber for the INDEX and then the 25 is the column number


BTW I gues you found out how to extend the arrays I forgot to mention that I had only allowed for 110 rows, little point in searching thousands of blank rows.

Brian

 

[mattg]

i am going to have to call it a day for a few days

nothing is working now , in the attempt to reduce my work by 30mins a day has now increased to 2hrs aday as i have to do it all manualy now.

none of my vlookups are working now and not even my access sheet will load any figures

its all got a bit too much and i need to get my original goals back on track

give me a few days to try and sort it all out

i understand how you have created the index now, well more than i did yesterday

i thank you kindly brian and may return once i have sorted out my sheets

 

[Brianwarnock]

I'm also going to be tied up for a couple of days but can you not attach your Workbook(s) for me to look at I'm sure there must be a simple explanation (famous last words )

Brian

 

[mattg]

hello brian,
i cant upload my workbooks here as the contents is very valuble in the right hands.

ill be back in a few days , ill read my book a bit more so i have a better understanding

using your input i have been able to make my formulars alot better now

again i thank you

 

[mattg]

hello brian

i have got it all working now. everything i required is now functioning great and is saving me loads of time, which i am truely greatfull for.

i sat down and read all that had been posted. i was getting confused with the array meanings, but now i understand and once i set all the named areas correctly it all started to work.

it has helped me understand how to use the other formulars i use .

again i cant thank you enought.

takecare. matt