Forum Discussion
formula needed for VLOOKUP
- Hi, is it possible to lookup for price only in column C, but to search in columns A and C, but exclude column B. Thx
HansVogelaar
Hi, thank you. I have tried for another table, see attachment.
The formula should be from FL program and Expenses per Week.
In the column Parking should look up from Column "D" if DUS or LEJ and check price from sheet "Expenses per Week" from A3 to B4, Like if DUS than 888 and if LEJ than 999 should automatically be display at the column in "Parking" in sheet FL program.
Looking forward
P.S. Can then i just copy free to another tables and what should i take attention to.
There are two problems:
- Cell A4 on the EXPENSES sheet contains a space after DUS: "DUS " instead of "DUS". This prevents the VLOOKUP formula from finding a match for "DUS". Remove that space.
- There are spaces at the end of the name of the EXPENSES sheet: it is "EXPENSES_PER_WEEK " instead of "EXPENSES_PER_WEEK". Remove those spaces from the name of the sheet and from the VLOOKUP formulas.
Also, I'd use $A$3:$B$4 as lookup range. That way, you can copy the formula up or down and the range will stay the same.
After making the changes in 1. and 2. above, Select AH7:AH53 on FL_Program.
Enter the formula
=IFERROR(VLOOKUP(D7,EXPENSES_PER_WEEK!$A$3:$B$4,2,FALSE),"")
In German:
=WENNFEHLER(SVERWEIS(D7;EXPENSES_PER_WEEK!$A$3:$B$4;2;FALSCH);"")
and press Ctrl+Enter to populate all selected cells. Excel will automatically adjust D7 to D8 ... D53 in the rows below, while keeping $A$3:$B$4 the same.
Another problem: the formulas in column S perform a lookup of the currency in column R in R1:S2. But R1 and R2 contain 111 and 110, not USD or similar. I don't know what you intended there.
See the attached version.
P.S. M1 contained a #REF! error so I entered 1,18
- Hi, thanks a lot for ur cooperation.
Is it possible to create a formula which uses VLOOKUP from differnt excel file?
Thank youYes. that is possible. Here are the steps:
Open both the workbook with the lookup range and the workbook where you want the formula.
Select the cell where you want the formula.
Start typing the formula, for example
=SVERWEIS(A2;
Now use the mouse or the keyboard to switch to the other workbook.
Activate the sheet with the lookup range.
Select the lookup range. You will see something like
=SVERWEIS(A2;'[ExterneDatei.xlsx]Blatt 2'!$A$2:$B$100
Complete the formula the normal way:
=SVERWEIS(A2;'[ExterneDatei.xlsx]Blatt 2'!$A$2:$B$100;2;FALSCH)
Press Enter and check whether it returns the expected result.
If it does, switch to the other workbook and close it. The formula will now include the path of the other workbook:
=SVERWEIS(A2;'C:\Excel\[ExterneDatei.xlsx]Blatt 2'!$A$2:$B$100;2;FALSCH)
Save the workbook.
- Hi, is there any possibilty to check as well from another excel table, to use the formula only and place figure if currency in dedicated column in in EUR, if not than figure 0.
Of course than we have to make sure, that the currency in column is set up to currency EURO from settings.
Thx