Return a Price Based on Two City Names in Excel - WPS Spreadsheets
Question details
The user needs a formula or lookup method in a WPS Spreadsheets workbook that returns a price when two city names, such as STOCKHOLM and NORRKÖPING, are entered in separate cells.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building or editing a price list in WPS Spreadsheets and wants the correct price to be returned automatically from a two-city combination.
- Observed behavior
- The result cell should display the matching price when both entered city names match the combination; otherwise it should stay blank.
Identify the two city input cells and create or locate a price lookup area with City 1, City 2, and Price columns. Make sure city names are spelled and capitalized consistently, including special characters such as Ö in NORRKÖPING.
Look up prices for multiple city pairs with a two-city lookup table and XLOOKUP or INDEX/MATCH
Use this when you have many city combinations and want a scalable, easy-to-maintain price table.
A lookup table is easier to update than nested IF formulas. WPS Spreadsheets supports XLOOKUP and INDEX/MATCH, and you can use a helper key that combines both city names to return the matching price.
In a WPS Spreadsheets sheet, click a blank area and enter three column headers: City 1, City 2, and Price. Then list every city pair and its price, for example STOCKHOLM in D2, NORRKÖPING in E2, and 250 in F2.
Click the cell where you want the price, type =XLOOKUP(A2&B2,$D$2:$D$100&$E$2:$E$100,$F$2:$F$100,""), and press Enter. A2 and B2 are the two city input cells.
Type =INDEX($F$2:$F$100,MATCH(1,($D$2:$D$100=A2)*($E$2:$E$100=B2),0)) and press Ctrl+Shift+Enter to enter it as an array formula if prompted.
Return a fixed price for a specific city pair with IF and AND
Use this for a small number of fixed city pairs, such as one route or one delivery zone.
Return a two-city price with WPS Spreadsheets
Create a two-city price lookup directly in WPS Spreadsheets. Use a helper key with XLOOKUP or INDEX/MATCH to return the matching price automatically, or use IF and AND for a single fixed route.
- 1. Open WPS Spreadsheets: Open WPS Office, click Spreadsheets, and then open or create the workbook that contains the two city input cells.
- 2. Prepare the price lookup table: Enter City 1, City 2, and Price as column headers, and list each city pair with its corresponding price.
- 3. Enter the lookup formula: Click the price result cell, type =XLOOKUP(A2&B2,$D$2:$D$100&$E$2:$E$100,$F$2:$F$100,""), and press Enter.
- 4. Check and reuse the formula: Change the city names in A2 and B2 to confirm the returned price updates, then copy the formula down with the fill handle if you have multiple rows.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP?
Yes, current WPS Spreadsheets versions support XLOOKUP. If your version does not show XLOOKUP, use INDEX and MATCH: =INDEX($F$2:$F$100,MATCH(1,($D$2:$D$100=A2)*($E$2:$E$100=B2),0)).
Why does my formula return blank when the city names look correct?
Check for extra spaces, case differences, or special characters. Use =TRIM(A2) and =TRIM(B2) or make the lookup table values match exactly, including characters such as Ö in NORRKÖPING.
How can I avoid writing a long formula for many city combinations?
Create a separate lookup table with City 1, City 2, and Price columns, then use XLOOKUP or INDEX/MATCH. This keeps the formula short and makes price changes easier.
Will the price update automatically when I change one of the city cells?
Yes, worksheet formulas recalculate automatically. After you change A2 or B2, the result cell will update to the matching price or return blank if no match exists.
