Vlookup #REF error in formula

[oxicottin]

Hello, im trying to reference a chart on my sheet2 using VLOOKUP and I have no clu where to add the name of the sheet. (C5,F5 are on sheet2 and Q5:U263 is on sheet2)

=IF(G36="","",IF(G36<C5,F5,VLOOKUP(G36,Q5:U263,4,1)))

Thanks!

[:) Sixthsense :)]

(C5,F5 are on sheet2 and Q5:U263 is on sheet2)

All references are in sheet2 or typo error?

[FDibbins]

I would suggest that you create the formula using the "point and click" method. that way, the correct sheet references will be added for you

[oxicottin]

I would suggest that you create the formula using the "point and click" method. that way, the correct sheet references will be added for you

I did click on the sheet where I thought it should be an I still go the error. the formula works if its on the same sheet . here is what I used/tried with same error.

 =IF(G36="","",IF(G36<'ReferanceSheet'!C5,F5,VLOOKUP(G36,'ReferanceSheet'!Q5:U263,4,1)))

[Fotis1991]

My suggestion is to upload a sample workbook so anyone that want to helps you, can work on it.

[oxicottin]

OK I'll upload one in the morning when I get up.

Thanks!

[Fotis1991]

If you upload now, i believe that when you'll get up in the morning you'll have your solution!

Isn't this a good idea?

[Muzza68]

vlookup can be a real ***** to make work sometimes.
Check the type of your data, if it has been pasted from elsewhere it needs to be paste special as "unicode text".
Otherwise go to the data menu ribbon and do "Text to colums", select each column individually and just click finish without changing anything.
Also be careful your workbook didn't start out life as a .CSV file.

Even better is to use INDEX_MATCH for all your lookups.
=VLOOKUP(G36,'ReferanceSheet'!Q5:U263,4,1) equates to =Index([range of data for return],MATCH([lookup value],[lookup range],0),[column # of return])

[oxicottin]

Ok, here is the workbook attached. The sheet(Calculator) has a cell (G38)that supposed to interact with the sheet (Benefit Rate Tbl)When I add the sheets name by clicking it now gives me an error #NA.

Thanks!


Sorry about the sheet being protected I reuploaded also, I fixed the cell locations

[Fotis1991]

Your sheet is protected by a password.


Also range Q5:U263 in both sheets is empty.

[Fotis1991]

OR, do you mean?

=IF(G36="","",IF(G36<C5,F5,VLOOKUP(G36,Base_Period_Chart!C5:G259,4,1)))

[oxicottin]

Im so sorry I reuploaded without protect and fixed the cells to the correct ones. It kinda works now, what doesnt work is if the amount in G36 is less than 2,200.00 then G38 should display INELEGABLE but instead it shows a zero.

Fixed cells to:

=IF(G36="","",IF(G36<'Benefit Rate Tbl'!C5,F5,VLOOKUP(G36,'Benefit Rate Tbl'!C5:F263,4,1)))

[Fotis1991]

=IF(G36="","",IF(G36<'Benefit Rate Tbl'!C5,'Benefit Rate Tbl'!$F$5,VLOOKUP(G36,'Benefit Rate Tbl'!C5:F263,4,1)))

[oxicottin]

=IF(G36="","",IF(G36<'Benefit Rate Tbl'!C5,'Benefit Rate Tbl'!$F$5,VLOOKUP(G36,'Benefit Rate Tbl'!C5:F263,4,1)))

You sir are awesome! Thanks a mill.....

[Fotis1991]

You are welcome!

Thanks for the reb*