Excel - lookup and match/index

[drhunter]

Hi guys,

I have a problem i hope you can help me with.

here is an example,

cpty ccy country name1 name2
bin1 eur it Italy1 Italy2
bin1 eur de Germany1 Germany2
bin2 usd us America1 America2
bin2 eur dk Denmark1 Denmark2
bin3 eur fr France1 France2
bin3 usd us America1 America2
bin3 gbp gb England1 England2
bin4 yen jp Japan1 Japan2


the user is then asked to input:
CPTY:
CCY:
Country:

and the function is supposed to output:

name1:
name2:

is there a way of combining vlookup, match and index in order correc
result depending on the input field. As you can see in some cases ther
is more than 1 occurance of the CPTY, CCY.

Any help will be very much appretiated.

See attached spreadshee

 

[Frank Kabel]

Hi
one way:
- first create a helper column (lets say a new column A) which
concatenates the first three columns with the formula: =B1 & C2 & D1
- copy this formula down. If you like, you can hide this column
- If your user has entered the theree parameters in cells H1:H3 use the
following formula to get name1:
=VLOOKUP(H1 & H2 & H3,$A$1:$F$99,5,0)
- to get name2 use
=VLOOKUP(H1 & H2 & H3,$A$1:$F$99,6,0)

 

[Earl Kiosterud]

Dr,

One way is with Data - Filter - Autofilter. The user opens cpty, and
selects the criteria (e.g.: bin2), then opens ccy, selects, usd, etc. This
will reduce the list, and eventually select the record(s) that match the
three criteria. Use "all" to return to displaying all the records.

Another way would be to use Data - Filter - Advanced filter command. This
has the option of copying the record(s) found to another location.
Depending on the layout of your worksheet, you can have the user enter the
three criteria right into the criteria range required by the Advanced filter
command. Otherwise, you can have links in the criteria range to the cells
the user will use. Then the command must be run (it isn't dynamic -- you
have to do command every time the data changes. Either the user can run it,
or if it has to be user-proofed, have a macro run it. .

 

[Tom Ogilvy]

3 user entries are in M2, M3 and M4
Data in A2:E9 (headers in row 1)

=INDEX($D$2:$D$9,MATCH(M2&M3&M44,$A$2:$A$9&$B$2:$B$9&$C$2:$C$9,0),1)

Enter with Control+shift+enter rather than enter since this is an array
formula

for the data in E, Change D29 to E2:E9.

 

[drhunter]

Thanks guys,

I have got it to work. I adore you all. and thanks for all your help