[Solved] VLOOKUP Help
I am using a vlookup formula to populate textboxes, my lookup function looks a cell then looks up the matching values on another sheet and returns those values to textboxes.
My question is, Is it at all possible to get the vlookup function to refer to 2 cells?
Below is the function i am using, it looks at K7 i also want it to look at K5.
=VLOOKUP(K7,$Recipes.B2:H80,4,
or is it possible to link a textbox to 2 different cells? IE: X4 and Z4
Thanks for any help in advance
thankyou for your reply i have been playing with different ways for hours, can not get anything to work will show code below.
=VLOOKUP(K5&” “&K7,$Recipes.B2:H80,5,
thanks again for your reply mark_t
when you say create an index column how do i do that?
sorry for the stupid question, i have not come across this before.
It seems the array formula was not working with the “AND” function, but I was able to get it working with:-
=INDEX ( $Recipes.$A$1:$G$100,MATCH ( 2, ( $A$1=$Recipes.$A$1:$A$100 ) +( $B$1=$Recipes.$B$1:$B$100 ) , 0 ), 4 )
This will search for the first row where both A1 is in column A of the Recipes sheet and B1 is in column B of the Recipes sheet, and return the value from column 4 of that row.
i will put this to the test.
sorry for the late reply back at work now.
thanks again for your help
ok i put the formula in cell X20 and pressed ctrl, shift and enter.
but the only thing i get in my text box that i linked to X20 is a “0”
any ideas what i am doing wrong.
do i need to add the new column thing “=B2 & C2”
ok i took your code and changed it around abit, and it still doesn’t work see my code below.
=IF ( ISERROR(VLOOKUP(K7,$Recipes.B2:H80,4,0 )) , VLOOKUP(K5,$Recipes.B2:H80,4,0 ), VLOOKUP (K7,$Recipes.B2:H80,4,0 ))
I put “=K7 & K5” in cell X26
Then i used my VLOOKUP formula to look at X26, and it worked, now i can use my search feature or my other list option. See formula below.
=VLOOKUP ( X26,$Recipes.B2:H80,4,0 )
I think the reason the text box did not work might be that you used ctrl-shift-enter, but you should not need ctrl-shift-enter for this expression as it returns only a single value. It should still work in a cell with or without ctrl-shift-enter, but it might stop the text box from picking up the value.
I am not sure what has gone wrong here, this works fine in my test book, I have transferred all the code etc to my actual workbook, and now the search is searching my ingredients. it only looks at the recipe name. any ideas I have attached my actual workbook.
The 2 white cells are the cells in question. the 2 green cells work perfectly fine.
my vlookup is located at X4 to X12, and x13 contains “=I4 & I3”
I cant see the vlookup being the problem as it functions perfectly well in the testbook.
For the correct results to be shown in the textboxes vlookup must look at cells I4 and I3
otherwise textboxes wont populate.
Thanks in advance
| Attachment | Size |
|---|---|
| AOORecipeBook.5.3.ods | 0 bytes |
Sorry for the late reply busy with work.
I looked over and over my workbook, and finally realised that I had a hidden column on the Recipes sheet, so I amended the code to search column 3 and it is working fine again now.
thanks again for your reply and all your help so far.
I’ve used this method in Excel, it should also work in LibreOffice.
It might be possible to use array formula, but I couldn’t get it to work.
Don’t forget to include spaces before and after parenthesis in posts or you start to see emoticons or sometimes missing text.