vlookup and hlookup mix.....

[Manos]

Dear all,

i have in a first column names (Lagos, Benin, Agbara,
Russia, ...)
then i have 12 columns which have figures for each month
(e.g. B--> January, C--> February, D--> March,...)

In the sheet 2 i want to use a function which will search
either by name or by month and bring me the respectivly
number.
I tried to use a mix of VLOOKUP and HLOOKUP, but it did
not work. It works only the vlookup function or the
hlookup function. not a combination of both.
The problem is that i want to use a mix because i am
afraid of user to add any columns between the existing and
so the vlookup will bring me wrong numbers. The same exist
of hlookup function.
One solution is to lock the cells in order to avoid
entering new lines or columns, but if the sheet is lock,
the system can not retrieve data from the users system..

Does anyone have an idea how can i use a mix of vlookup
with hookup, or any other way to bring the correct numbers
from each country for each month?

Thanks in advance
Manos

 

[Frank Kabel]

Hi Manos
one way (if A1 is your lookup value for the country and B1 the month
lookup value):
=INDEX('sheet1'!A1:M100,MATCH(A1,'sheet1'!A1:A100,0),MATCH(B1,'sheet1'!
A1:M1,0))

 

[Manos]

Hi Frank
It works perfectly. thanks a lot mate!!!!
there is anyway to insert the ISERROR function
in case ther are some blank cells.
If there is a blank cell it gives me the #N/A
Thanks a lot again
Have a nice day

 

[Frank Kabel]

Hi
one way
=IF(ISNA(INDEX('sheet1'!A1:M100,MATCH(A1,'sheet1'!A1:A100,0),MATCH(B1,'
sheet1'!A1:M1,0)),"",INDEX('sheet1'!A1:M100,MATCH(A1,'sheet1'!A1:A100,0
),MATCH(B1,'sheet1'!A1:M1,0)))

 

[Manos]

For one more time "thanks a lot Frank"

-----Original Message-----
Hi
one way
=IF(ISNA(INDEX('sheet1'!A1:M100,MATCH(A1,'sheet1'! A1:A100,0),MATCH(B1,'
(A1,'sheet1'!A1:A100,0
),MATCH(B1,'sheet1'!A1:M1,0)))


--
Regards
Frank Kabel
Frankfurt, Germany



.