Multiple vertical & horizontal lookup problem

[Hiruy]

Hi

I’m in need of a way of cross checking amount collect collected and price published. The ‘sales’ excel contains the sales data for September while the published Fares are ‘Fares’ excel.
- The Fares are published on origin- destination basis with respect to Classes designated by alphabets.


What I would like to do is bring the corresponding published fare of each sales line to the V column of the spreadsheet from the ‘fares’ excel using the criteria given on the G,H & I columns). Pls help me.

[6StringJazzer]

Use this formula in V2 of Sales.xlsx and copy down. This is an array formula. To enter an array formula, do not hit ENTER after typing the formula--use CTRL+SHIFT+ENTER.

=INDEX([Fares.xlsx]Sheet2!$C$2:$Q$118,MATCH(H2&I2,[Fares.xlsx]Sheet2!$A$2:$A$118&[Fares.xlsx]Sheet2!$B$2:$B$118,0),MATCH(G2,[Fares.xlsx]Sheet2!$C$1:$Q$1,0))

See attachment for implementation.

This assumes that your two files are in the same folder. Note that there are many #N/A errors where the origin/destination combination does not exist in the Fares file.

[Syrkrasi]

Try this

For the Fares workbook I used Range naming convention

• Range(C1:Q1) = Fares_Classes
• Range(A2:A118) = Fares_Origins
• Range(B2:B118) = Fares_Destinations

Calculation used in the Sales workbook

=IFERROR(INDEX(SK_Fares.xlsx!Fares_ClassNums,MATCH(H2&I2,SK_Fares.xlsx!Fares_Origins&SK_Fares.xlsx!Fares_Destinations,0),MATCH(G2,SK_Fares.xlsx!Fares_Classes,0)),"")

This is an array formula so you MUST press the SHIFT + CTRL + ENTER keys simultaneously in order for the formula to work

Note for illustration, I did not use the array method of using the SHIFT + CTRL + ENTER keys to activate the formula. Try it for yourself by selecting field V2, then press the SHIFT + CTRL + ENTER keys simultaneously


see attachments