Do a lookup with VBA based on criteria in three cells

[S Davis]

Hello,

This is a bit tricky to explain. Forgive me, I'll do my best.

Based on the contents of three cells on one sheet, I would like to
know the location (cell reference, ie $B$33) of the item desired, and
then return the contents of the cell immediately adjacent to it.

Let's say the cells I want to look up are here:
A1=CommandX
A2=Room1
A3=Object3

I want to return "Desc" from another worksheet in the below example:


------------------Room1--------Description---------Room2--------
Description ..... RoomN
CommandX---Object1-------Desc------------------ObjectN+1---Desc.....
CommandX---Object2-------Desc------------------ ....
CommandX---Object3-------Desc...
.....
CommandY---ObjectN------Desc...
.......
CommandZ.........


So its not terribly pretty.

In english, the code would need to find the intercept of matching
Command and Room, find the Object for those, identify the cell
reference of that Object, and then return Desc from the column
immediately to the right.

Is this posssible?

 

[S Davis]

If it helps any, "Commands" and "Rooms" are named ranges (ie. commands
= A1:65000; rooms = B2:II2)

 

[Max]

In english, the code would need to find the intercept of matching

Command and Room, find the Object for those, identify the cell
reference of that Object, and then return Desc from the column
immediately to the right.

Assuming the source sheet (as posted) is named simply as: x
with Room1, Desc, Room2, etc listed in B1 across
and CommandX, etc listed in A2 down

then in the sheet where you have 3 lookup var listed in A1:A3, eg:

A1=CommandX
A2=Room1
A3=Object3

you could place this in say, A4,
array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=INDEX(OFFSET(x!1:1,MATCH(1,(x!A1:A100=A1)*(OFFSET(x!A1:A100,,MATCH(A2,x!1:1,0)-1)=A3),0)-1,),MATCH(A2,x!1:1,0)+1)
to return the required result from the description col adjacent to the
"Room#"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---