table/s

[trey]

i have 2 drop down menu/s.. the list is on another
sheet.. the drop down's on my form...
ex:
Type YR 1 2 3
1% / 1mo Int 1 40 30 20
2% / 2mo Int 2 35 25 10
3% / 3mo Int 3 30 15 0
4% / 4mo Int 25 10 10
5% / 6mo Int 20 0 20
3-2-1% 30 20 10
5-4-3% 20 5 15
- when you choose 5%/6mo Int and 2yrs = " 0 " with the 0
being assigned to a specific cell on my form...
what formula do i need to use the vlookup?
presently the drop down menu's on my form are: j29,k29
the " type " listsheet3 u6-u12... the " yr " list: ac6-
ac8... and the table is y6:aa12... im very confused...
plz help...
thanks
trey

 

[Frank Kabel]

Hi
hope I got your cell references right. Try the array formula (entered
with CTRL+SHIFT+ENTER):
=INDEX('listsheet3'!Y6:Y12,MATCH(1,('listsheet3'!U6:u12=J29)*('listshee
t3'!AC6:AC12),0))

Note: the years columns has to be filled for all rows

 

[Domenic]

My interpretation is a little bit different than Frank's -- hi Frank!

Assuming that your headers for the data in listsheet3 are contained in
Row 5, and that your actual data starts in Row 6, try the following
formula:

=INDEX(listsheet3!Y6:AA12,MATCH(Sheet1!J29,listsheet3!U6:U12,0),MATCH(She
et1!K29,listsheet3!Y5:AA5,0))

Hope this helps!

 

[trey]

when you say this: the years columns has to be filled for
all rows: what are you telling me to do...
thanks
trey

 

[Frank Kabel]

Hi Doemnic
thought this also but decided to go the other way

So lets see what the OP needs

 

[Frank Kabel]

Hi
what I meant is that your column AC should contain a year value for all
rows. But see also Domenic's post. Not sure against what you want to
compare your year value agains (column heading or AC column)

 

[trey]

when i enter in the formula and hit enter it prompts me
to find a file... confusing...plz help... row 5 is
labeled only for my reference-no headers- though since
this is a validated list...
trey

 

[Domenic]

Hi Doemnic
thought this also but decided to go the other way

So lets see what the OP needs

I'm waiting with baded breath. (I think I've got the spelling
wrong for "baded" -- I'll have to check my dictionary.)

 

[Domenic]

when i enter in the formula and hit enter it prompts me
to find a file... confusing...plz help... row 5 is
labeled only for my reference-no headers- though since
this is a validated list...
trey

That's because it's trying to find "listsheet3", which is specified in
the formula. So make sure that the sheet reference in the formula
matches the name of your sheet exactly.

 

[Domenic]

I'm waiting with baded breath. (I think I've got the spelling
wrong for "baded" -- I'll have to check my dictionary.)

Ahh yes...that should be bated, not baded. Oh well, can't win them all.

 

[trey]

=INDEX(sheet3!Y6:AA12,MATCH(PREQUAL!J29,sheet3!
U6:U12,0),MATCH(PREQUAL!K29,sheet3!Y5:AA5,0))
it matches everythis in my workbook and it still does the
search? confused...any reason why it still may be doing
that.
trey

 

[Domenic]

=INDEX(sheet3!Y6:AA12,MATCH(PREQUAL!J29,sheet3!
U6:U12,0),MATCH(PREQUAL!K29,sheet3!Y5:AA5,0))
it matches everythis in my workbook and it still does the
search? confused...any reason why it still may be doing
that.
trey

Check your tab and make sure that there are no spaces before or after
the sheet name. Otherwise, I don't know why it would still be
happening. Maybe Frank or someone else has some other ideas.

 

[trey]

you were right there was a space..corrected and the
formula took... it is giving me an error: #n/a though..
any thoughts....

 

[trey]

it works..yippee

-----Original Message-----


Check your tab and make sure that there are no spaces before or after
the sheet name. Otherwise, I don't know why it would still be
happening. Maybe Frank or someone else has some other ideas.
.