Lookup Help

[EB08]

Not sure which way to go or if it can be done. Is it possible to search for a value in column "A" (lets say its found in row 21), then search row 21 for another value (lets say it's found in column "D") and pull the value in the cell below that. So in this case, i'm looking for the value in "D22". I know this is vague and terrible example and I apologize for that, i'm having a difficult time finding a way to make this work and i'm really hoping it can be done. Thanks again.

 

[Dougie1]

Use the Match/Index function.

In D22 insert :



=INDEX($A$1:$D$21, MATCH("??",$A$1:$A$21,),4)

Where ?? = the text or numerical value that you are looking for in column A

and 4 is the numeric representation of column D

Hope this helps.

D

 

[EB08]

It appears that I forgot to mention that after i find which row the value i first looked for is in, i still don't know which column is going to house the second value i'm searching for as this data is linked to other numbers that will change. For example, I look for "apple" in column "A"; "apple" in this example is found in row 21; Next i want to look for 100 in the same row "apple" is found (21); this is where it can change, one instance it can be in column "D", the next time column "B"; i need to find 100 in the same row as "apple" and pull the cell value thats below 100. I hope i didn't just confuse things further! thanks for the help though.

 

[jasonb75]

EB08, try this on a copy of your worksheet in case things go wrong, I have it working, but seem to have had a week of things that work for me fail for others.

First insert a new column B, and fill each cell with the row number to the end of the range (see table) then hide column B to keep it tidy.

Excel Workbook
	A	B	C	D	E	F
1	a	1	20	40		g
2	b	2	19	39		35
3	c	3	18	38		47
4	d	4	17	37
5	f	5	16	36
6	g	6	15	35
7	h	7	14	47
8	i	8	13	33
9	j	9	12	32
Sheet10

 

[jasonb75]

In the formula *(x?) shows how many times this Cell reference is found in the formula.*

F1 should be the cell with the text to look for in column A (x2)
F2 should be the value to look for in the row where that text entry is found (x1)
A1:D9 Should be the full range of your table (x2)
&":D"& should be changed so that it matches the last column of your table, so if your table is in column A:H then you change this to &":H"&

The same applies to "A"& if A is not your first column (with the names) you need to change this to match.

Hope this does the trick.

 

[EB08]

works for me so far...great solution! I ended up creating a vb solution in between posting and getting your response as it was easy to think out logically, i just couldnt put it in a formula. Thanks again!

 

[jasonb75]

I was close to resorting to VBA solutions as well, like you said the logic is easy to see, but the formula took a few attempts before it worked.

Now you have 2 ways to solve the problem

Would you mind posting your VB code, I would be interested to see how you've done it.

JB

 

[jasonb75]

Just a quick addition to this, I had another quick look at the formula and came up with

=HLOOKUP(E2,INDIRECT("A"&MATCH(E1,A:A,0)&":C"&(MATCH(E1,A:A,0)+1)),2,FALSE)

This one works without the need for the helper column B

Excel Workbook
	A	B	C	D	E
1	a	20	40		g
2	b	19	39		35
3	c	18	38		47
4	d	17	37
5	f	16	36
6	g	15	35
7	h	14	47
8	i	13	33
9	j	12	32
Sheet10

 

[EB08]

I don't have access to it over the weekend as it was on my work computer, but i'll just attempt to step through as i remember it (might be tough to remember, the code did a lot more before actually doing this). The first lookup i was doing was to be found in W2:W20, the second lookup was in the row the first lookup is found, could be no more than 10 columns over. then i had to offset it 1 down. Lets just say the first value i was looking up was in a1, the second in a2. So it looked something like...



for a=2 to 20
if cells(a,"W")=range("a1") then
rw=cells(a,"W").row
end if
next a

for a= 25 to 34
if cells(rw,a)=range("a2") then
cells(rw,a).offset(1,0).copy
range("a3").pastespecial xlpastevalues
end if
next a


This is just a rough draft from memory butI will post the actual code when i get to work on monday. This would get anybody started who was looking to do the same thing with vb. let me know if you need me to elaborate on anything i posted. Thanks again.

 

[jasonb75]

Thanks EB, I can see the logic in your code, loop through the first column looking for the first value, then loop through the row where it's found for the second value and return the value from the offset cell when it's found.

More than anything I was curious to see how you went about tackling it, your method is quite similar to what I would have probably ended up doing, although I'm sure the MVP's on the forum would cringe at that and come out with something completely different.

The thing I'm looking at now is to see if my formula can be shortened again, attempts so far have failed, but I'm sure it can be done