Lookup in raw and column concurrently

[Dmitry Kopnichev]

Hello
Is there a function
NameOfFunctionAnalogOfLookup(lookup_valueInFirstColumn,lookup_valueInFirstRo
w,table_array) or an analog of
VLOOKUP(lookup_value,table_array,MATCH(reference))?

 

[Jerry W. Lewis]

What you are looking for is not clear to me. Can you describe it in
English?

 

[ΓΙΑÎÎΗΣ Χ.Î’.]

If your column A4:A13 and your row A2:J2
Vlookup from column to row:

{=VLOOKUP(C4;CHOOSE({1;2};A4:A13;TRANSPOSE(A2:J2));2;0)}


Vlookup from row to column:

{=VLOOKUP(C4;CHOOSE({2;1};A4:A13;TRANSPOSE(A2:J2));2;0)}


of course C4 = Lookup_value

Ioannis Varlamis ,Athens, Greece

 

[Ken Wright]

Try something like this:-

Assuming your values in A2:J10, with row/col headers in A2:A10 and B1:J1

and your look up values in A16 and A17

=INDEX($A$1:$J$10,MATCH(A16,$A$1:$A$10,0),MATCH(A17,$A$1:$J$1,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[elitewolverine]

having something of the same probelm of this guy and didnt want t
create a new thread about it....

at work we use spreadsheets to track daily sales weekly sales, +/- fo
the week day and lots of other things related to work...

and after finding that we loose lots of things everynow and then an
want it to be easier to look up stuff while at the same time keepin
records i have been slowly modifying the spreedsheet to be easier t
use and very little input needed...

we track stuff by periods and weeks, where there is 4 weeks in on
period, for the year...for example our store is currently in Period 11
Week 1

well one thing that is hard to track is Cash for the Period...

so in the spreedsheet, i made another sheet called Sheet3, this way i
doesnt interfer with the other sheets and i can edit it at will, its m
test sheet...

well in the main Sheet i want one cell to update the Cash for th
Period according to whatever my boss enters in the Period/Wee
cells...

So, the cell i want to add the formula to is named, (+ / - PTD).

at the top of the sheet i have a cell name "Period" with the next cel
to it to enter the period and the same with the cell named "Week"

Period Cell is: B1
Week Cell is: D1

Sheet3, has the column for period as: A2 threw A17
and the Week row as: B2 threw E4

example...if we are in period 11 week 4, i want the cell named PTD, t
do a lookup on sheet3 match the period, match the week and give me th
value where they intersect at...

currently ive gone threw some functions but all with errors.

i think or acctually know the Index function is probably what i want
but my mind is so sore from formulas after two days of 3d coding an
this that i just cant think straight...

thanks for your tim

 

[Dmitry Kopnichev]

I found what I wanted. It is =INDEX('2004'!$A$11:$DV$1437;
MATCH($A28;'2004'!$A$11:$A$1437;0); MATCH(J$3;'2004'!$A$11:$DV$11;0)). Is
there a one function expression?

 

[Dmitry Kopnichev]

Thank you, Ken Wright!

Try something like this:-

Assuming your values in A2:J10, with row/col headers in A2:A10 and B1:J1

and your look up values in A16 and A17

=INDEX($A$1:$J$10,MATCH(A16,$A$1:$A$10,0),MATCH(A17,$A$1:$J$1,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­------------ ----
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­------------ ----