Value match in a table plus formula?

[Chancy]

I'm pretty sure the terms I used are off, but I don't know what's right.


First, this formula works, but I want to use it PLUS A FORMULA (all in one formula):

IFERROR(VLOOKUP(VALUE(A1),Codes!$A$1:$B$30,2,0),VL OOKUP(TEXT(A1,0),Codes!$A$1:$B$30,2,0))

In the Codes tab, Col B data is text. In what I want to do but don't know how, Col B data can be either numeral or alpha (whatever makes this work).

So this part I have
[(Look up A1 in Table, when you find it, if resulting match is Y (yes) (or numeral, such as 2?),

and then part I do not have:

then [B2*2]]

Is this possible and what would the correct formula be? To try to be more clear, I don't want to just populate the cell with a value, I want to populate the cell with the result from a formula after the VLOOKUP part is completed.

Thanks

[NBVC]

Perhaps?

=IF(IFERROR(VLOOKUP(VALUE(A1),Codes!$A$1:$B$30,2,0),VLOOKUP(TEXT(A1,0),Codes!$A$1:$B$30,2,0))="Y",B2*2,0)

[Chancy]

Thank you so much for trying, but, it didn't work. I tried various ways to group the sections ... removed one of the VLOOKUP (text) sections to make it simpler, and I get either 0 (zero) or #N/A for all values.

[ArviLaanemets]

I reread the opening post several times, and I don't understand, what kind of data you have where, and what you want to get as result!

You have 2 pages.
On page Codes you have data in columns A and B.
On page NoName you have data in Column A, and you want to have a formula in some other column.

1. What type of data (numeric or text) you have in column NoName.A? Is the type always same, or it can vary?
2. What type of data you have in column Codes.A? Is the type always same, or it can vary?
3. For both columns, are real formats for cells same as cell format? In excel when you p.e. have a cell formatted as text with numeric value entered, and later you change the cell format to number, the entry remains a text until you edit the cell (and vice versa). To correct entry formats for a range:
a) when new format is numeric, save a value 1 or 0 into some free cell, copy it, select the range you want to correct formats at, and from dropdown menu select PasteSpecial.Multiply or PasteSpecial Add (depending the value you copied before);
b) when the new format is text, into free column on first datarow enter the formula like

="" & A1

, and copy down. Copy all cells with formula, and overwrite the values in column you want to correct formats at using PasteSpecial.Values;
4. Are data types always same in columns NoName.A and Codes.A?
5. What is data type in column Codes.B? From your post:

In the Codes tab, Col B data is text. In what I want to do but don't know how, Col B data can be either numeral or alpha (whatever makes this work).

Or do you mean NoName.B in second sentence of citation here?
6. What do you want get for result? The value from column B of sheet NoName multiplied with 2 or 0, depending on result of vlookup? Or something else?

[Chancy]

Thank you so much for trying!
I made an image to explain more clearly (hopefully).

Technically all of the data involved in this is numbers, but the use of both Value and Text in VLOOKUP is only because sometimes the cells get read properly and sometimes they don't (by Excel). I fix that by using both. This is not a problem and I get the correct results.

What I need help with now is that I want to add a second complete formula that multiples the Fee % found in the table times another cell value on the same row.

I have about 5000 rows of data for this project, in case that matters.

Hopefully this helps!

[NBVC]

If your formula gives you a correct result, then you just need to add the multiplication...

=IFERROR(VLOOKUP(VALUE(B2),Codes!$A$1:$B$30,2,0),VLOOKUP(TEXT(B2,0),Codes!$A$1:$B$30,2,0))*A2

[Chancy]

Originally Posted by NBVC

If your formula gives you a correct result, then you just need to add the multiplication...

=IFERROR(VLOOKUP(VALUE(B2),Codes!$A$1:$B$30,2,0),VLOOKUP(TEXT(B2,0),Codes!$A$1:$B$30,2,0))*A2

THUD.


Excel just cracks me up. I can build up such complicated factors unnecessarily!

Thank you ANGEL.

[Chancy]

FYI, This is what I currently have:

=ROUND(IFERROR(VLOOKUP(VALUE(P1398),FEE!$A$4:$B$118,2,0),VLOOKUP(TEXT(P1398,0),FEE!$A$4:$B$118,2,0))*(J1398),2)

Delighted to find that even for the entries that multiply times zero, the results are zero (not #N/A or #DIV).